get only last row in each day's 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 three rows for day 09, and two rows for day 10.

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

I cannot use distinct because of the timestamp. I cant group and use:

CAST(CONVERT(FLOAT, EnteredOn) AS INT)

because when I select EnteredOn field, it complaints that its not grouped. I cant combine distinct(cast..date...) because I cant get the right syntax.

How can I select - only Name, EnteredOn, Percentage fields with distinct to each day?

many thanks in advance.

Solution 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

Solution 2

1 row/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 row/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

Solution 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

Solution 4

I would suggest one more trick here:

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)

View more solutions

Comments

[iamserious]

Hi, this does work, however, I dont understand most of it, what is the query doing? esp partition - what does this do? does this query slow down on large number of rows? thanks

[iamserious]

also, this considers solely on date, sorry for not mentioning it before, I want to be able to filter on person too.

[iamserious]

adding to last comment select one row per day of a particular person and particular percentage - something like this..

[iamserious]

Thank you very much for helping me out :-)

[Mikael Eriksson]

@iamserious - The partition by is kind of instead of the group clause. row_number() increments for each row in the order specified by order by. partition determines when the row_number should start from 1 again. So if you need to group by Name as well you should extend partition to partition by datediff(d, 0, EnteredOn), Name and so on for more fields you need to group on. I can't say anything about performance other than that you should run tests with the queries suggested here and pick the one that has the best performance on your data.