Get only the last row of every day multiple entries in TSQL

Question

I have a table, something like:

Id Name EnteredOn Percentage ````````````````````````````````````````````````````````````` 01 person1 2011-03-09 17:29:35.683 56.29 02 person1 2011-03-09 17:29:35.731 76.29 03 person1 2011-03-09 18:15:78.683 56.29 04 person1 2011-03-10 17:29:35.683 56.29 05 person1 2011-03-10 16:29:31.683 56.29 06 person1 2011-03-11 17:29:35.683 56.29 

To summarize the above table, there are lines three for day 09 and two for day 10 . >.

Now I just want to select the last row - one row - per day.
(one line for 9, one for 10 and one for 11)

I cannot use different data due to timestamp. I can not use group:

 CAST(CONVERT(FLOAT, EnteredOn) AS INT) 

because when I select the EnteredOn field, it complains that it is not grouped. I cannot combine distinct(cast..date...) because I cannot get the correct syntax.

How can I choose - only the Name, Enter, Percentage fields that are different from each other?

Thank you very much in advance.

Answer 1

 ;with cte as ( select *, row_number() over(partition by datediff(d, 0, EnteredOn) order by EnteredOn desc) as rn from YourTable ) select * from cte where rn = 1 

Answer 2

1 line / day:

 SELECT t1.Name, t1.EnteredOn, t1.Percentage FROM table t1 JOIN (SELECT MAX(EnteredOn) Max_EnteredOn_By_Day FROM table GROUP BY convert(varchar, EnteredOn, 112)) t2 ON t1.EnteredOn = t2.Max_EnteredOn_By_Day 

1 line / person / day:

 SELECT t1.Name, t1.EnteredOn, t1.Percentage FROM table t1 JOIN (SELECT Name, MAX(EnteredOn) Max_EnteredOn_By_Day FROM table GROUP BY Name, convert(varchar, EnteredOn, 112)) t2 ON t1.Name = t2.Name AND t1.EnteredOn = t2.Max_EnteredOn_By_Day 

Answer 3

 SELECT Name, EnteredOn, Percentage FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY CONVERT(VARCHAR(8),EnteredOn,112) ORDER BY EnteredOn DESC) Corr FROM YourTable) A WHERE Corr = 1 

Answer 4

I would suggest one more trick:

 select top 1 with ties Name, EnteredOn, Percentage from YourTable order by row_number() over(partition by datediff(d, 0, EnteredOn) order by Name, EnteredOn desc)