SQL Server + Select top 1 record of all the distinct records

Question

I am struggling to write a query to result in the following records.

I have a table with records as

c1  c2            c3                  c4    c5   c6

1  John         2.3.2010 12:09:54     4     7    99        
2  mike         2.3.2010 13:09:59     8     6    88   
3  ahmad         2.3.2010 14:09:59     1     9    19   


4  Jim        23.3.2010 16:35:14      4     5    99   
5  run        23.3.2010 12:09:54      3     8    12 

I want to fetch only the records :-

3  ahmad         2.3.2010 14:09:59     1     9    19   
4  Jim        23.3.2010 16:35:14      4     5    99   

I mean the records that are sort by column c3 and the one which is latest for that day. here i have 1, 2, 3 records that are at different times of the day. there i need the records that are sort by date desc and then only top 1 record. similarly for 4 and 5. can you please help me in writing a query.

Answer 1

If you're on SQL Server 2008 or 2008 R2, you can try this:

WITH TopPerDay AS
(
   SELECT 
      c1, c2, c3, c4, c5, C6,
      ROW_NUMBER() OVER
          (PARTITION BY CAST(c3 AS DATE) ORDER BY c3 DESC) 'RowNum'
   FROM dbo.YourTable
)
SELECT * 
FROM TopPerday 
WHERE RowNum = 1

I basically partition the data by day (using the DATE type in SQL Server 2008 and up), and order by the c3 column in a descending order. This means, for every day, the oldest row will have RowNum = 1 - so I just select those rows from the Common Table Expression and I'm done.