SQL query to distinct one column which has different date

September 9, 2022

I have a SQL query as you can see

SELECT DISTINCT [WB_ID] FROM [dbo].[Entity]  
WHERE 
(
1 = CASE  WHEN audit_user_id LIKE'%' + '' + '%' THEN 1
WHEN '' = '' OR '' IS NULL THEN 1
ELSE 0
END  

AND  
1 = CASE  WHEN CONVERT(date, audit_date) BETWEEN CONVERT(date, '') AND CONVERT(date, '') THEN 1
WHEN '' = '' OR '' IS NULL THEN 1
ELSE 0  
END

AND  1 = CASE  WHEN audit_mode = '' THEN 1
WHEN '' = '' OR '' IS NULL OR '' = 'All' THEN 1
ELSE 0  
END
)

and the result is like

WB_ID
1864
1871
1873
1885
1886
1887
1888

each of these WB_ID in the table are repeat more than one with different "audit-date" .And then when I add "audit-date" and select like this

change select part to this

SELECT DISTINCT [WB_ID],audit_date FROM [dbo].[Entity]

my output is like this


| WB_ID | audit-date                    |
| ----- | ----------------------------- |
| 1864  | 2022-09-07 10:43:54.8600000   |
| 1864  | 2022-09-05 01:07:58.5730000   |
| 1871  | 2022-09-06 13:35:11.0430000   |
| 1871  | 2022-09-07 10:35:32.0870000   |
| 1871  | 2022-09-07 08:21:50.0900000   |
| 1871  | 2022-09-06 13:45:31.6800000   |
| 1873  | 2022-09-07 10:35:32.0870000   |
| 1873  | 2022-09-07 08:21:50.0900000   |
| 1873  | 2022-09-06 13:45:31.6800000   |
| 1885  | 2022-09-06 13:45:31.6800000   |
| 1885  | 2022-09-07 10:35:32.0870000   |
| 1885  | 2022-09-07 08:21:50.0900000   |
| 1885  | 2022-09-06 13:45:31.6800000   |
| 1886  | 2022-09-07 10:35:32.0870000   |
| 1886  | 2022-09-07 08:21:50.0900000   |
| 1886  | 2022-09-06 13:45:31.6800000   |
| 1887  | 2022-09-07 10:35:32.0870000   |
| 1887  | 2022-09-07 08:21:50.0900000   |
| 1887  | 2022-09-06 13:45:31.6800000   |
| 1888  | 2022-09-07 08:21:50.0900000   |
| 1888  | 2022-09-06 13:45:31.6800000   |

Now I want to show just Distinct "WB_ID" ,but ORDER it BY "audit-date" and show the one which has a newest date at the top

>Solution :

select   wb_id
from    (select  *
                 ,row_number() over(partition by wb_id order by audit_date desc) as rn
         from t) t
where    rn = 1
order by audit_date desc