Displaying one instance of each category SQL Query-CodePudding

'ENTRY_TYPE_NAME, STATE_NAME, TIMESTAMP',

'"log backup", "successful", "2022-07-25 12:11:20.965000000"',
'"complete data backup", "successful", "2022-07-22 11:39:56.757000000"',
'"complete data backup", "canceled", "2021-05-06 06:08:22.391000000"',
'"log backup", "failed", "2022-07-06 16:22:45.346000000"',
'"complete data backup", "failed", "2022-07-05 06:16:47.702000000"',

Lets say I have a table of this sort. I want to only print out the latest(according to timestamp) values of ENTRY_TYPE_NAME.

For Example, my desired output would be,

'ENTRY_TYPE_NAME, STATE_NAME, TIMESTAMP',
'"complete data backup", "Successful", "2022-07-22 11:39:56.757000000"',
'"log backup", "Successful", "2022-07-25 12:11:20.965000000"',

I tried using this query, select ENTRY_TYPE_NAME, MAX(UTC_END_TIME) as Timestamp from SYS.M_BACKUP_CATALOG GROUP BY ENTRY_TYPE_NAME but since complete data backup has 3 state names whereas log backup has only 2 I cant get my desired output.

Is there any other way to solve this?

CodePudding user response：

This can be achieved using PARTITION BY. example:

WITH tempdata as
{
    SELECT ROW_NUMBER() OVER(PARTITION BY ENTRY_TYPE_NAME
    ORDER BY TIMESTAMP DESC) as RowNumber,
    ENTRY_TYPE_NAME,
    STATE_NAME,
    TIMESTAMP
    FROM #temptable1
)
SELECT ENTRY_TYPE_NAME,STATE_NAME,TIMESTAMP FROM tempdata WHERE RowNumber = 1

where #temptable1 is your table name.

Here you can see example result set

Hope it helps.