Select Max Distinct Dates from list of Dates

Question

I have a table which is a list of dates with times. For example

Dates

1/3/2014 6:16:43.570 AM
1/3/2014 6:17:02.300 AM
1/3/2014 6:23:09.700 AM
1/6/2014 6:02:09.993 AM
1/6/2014 6:02:34.323 AM
1/6/2014 6:03:02.957 AM
1/6/2014 6:08:48.867 AM
1/6/2014 6:09:04.917 AM

What I want to end up with is:

1/3/2014 6:23:09.700 AM
1/6/2014 6:09:04.917 AM

since those are the most recent times for each date.

Any help would be greatly appreciated.

Answer 1

SELECT MAX(datetime)
FROM Dates
GROUP BY DATE(datetime)

Answer 2

You can use group by or row number. Here is row number to show you another way to do it. (Since others have shown the group by example)

SELECT d
FROM
(
   SELECT d,
          row_number() OVER (PARTITION BY convert(date,d) ORDER BY d DESC) as rownum
   FROM Dates
) t
WHERE rownum = 1