Sql table filter?

Question

i have a table ABC

 Id      Sub_id      Date

CS01032 Sub012  2011-09-21 16:29:58.853
CS01033 Sub013  2011-09-21 16:30:09.863
CS01033 Sub014  2011-09-21 16:30:12.113
CS01034 Sub015  2011-09-21 16:37:57.233
CS01035 Sub016  2011-09-21 16:51:52.527
CS01035 Sub017  2011-09-21 16:51:54.430
CS01035 Sub018  2011-09-21 16:51:56.333

i want filter this table data like

 Id      Sub_id      Date

CS01032 Sub012  2011-09-21 16:29:58.853 
CS01033 Sub014  2011-09-21 16:30:12.113
CS01034 Sub015  2011-09-21 16:37:57.233    
CS01035 Sub018  2011-09-21 16:51:56.333

means distinct id with top 1 sub_id order by Date time

Answer 1

Use a CTE and the ROW_NUMBER function:

;WITH TopData AS
( 
    SELECT  Id, Sub_Id, Date,
       ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Date DESC) AS 'RowNum')
    FROM
       dbo.ABC
)
SELECT Id, Sub_Id, Date
FROM TopData
WHERE RowNum = 1

The CTE (Common Table Expression) will "partition" your data by Id and give each group's entries ROW_NUMBER values, starting at 1, order by Date descending (newest date first). The first entry for each group - the most recent for each Id - has RowNum = 1