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

Question

Hello Everyone,

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.

Answer 2

Tried this on 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

Hello Everyone,

Thanks for the responses!

I have found the solution too.

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