SQL Server + Select the first 1 record of all individual records

Question

I am trying to write a query to get the following entries.

I have a table with entries like

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 get only 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 records sorted by column c3, and those that are last for this day. here I have 1, 2, 3 entries that are at different times of the day. there I need records sorted by desc date, and then only the first record. similarly for 4 and 5. could you please help me in writing the request.

Answer 1

If you are using SQL Server 2008 or 2008 R2, you can try the following:

 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 split the data by day (using the DATE type in SQL Server 2008 and above) and order it with the c3 column in descending order. This means that for each day, the oldest row will have RowNum = 1 - so I just select these rows from Common Table Expression, and I'm ready.

Answer 2

Tried this in a SQL Server 2005 database.

 SELECT * FROM dbo.YourTable t1 WHERE (t1.c3) = ( SELECT MAX(t2.c3) FROM dbo.YourTable t2 WHERE DATEDIFF(dd,t2.c3, t1.c3) = 0 ) ORDER BY t1.c3 ASC 

Answer 3

Thanks for answers!

I also found a solution.

 select * from (select convert(varchar(10),c3,104) as date, max(c3) as date1 from MYTABLE group by convert(varchar(10),c3,104)) as T1 innerjoin MYTABLE as T2 on convert(varchar(10),T2.c3,104) = T1.date and t2.c3 = T2.date1