Select the last entry recorded in a table for each day, within a duration of days

How can I select the last entry recorded for each day? In this example, I need the last item number ordered and the last DateOrdered entry for each day over the last 5 days. Here’s my table:

ItemNumber  |       DateOrdered
    1           2020-04-01 08:00:00.000
    3           2020-04-01 09:00:00.000
    5           2020-04-01 10:00:00.000
    4           2020-04-02 09:00:00.000
    6           2020-04-02 10:00:00.000
    7           2020-04-03 08:00:00.000
    3           2020-04-03 09:00:00.000
    2           2020-04-03 10:00:00.000
    5           2020-04-04 10:00:00.000
    8           2020-04-05 08:00:00.000
    2           2020-04-05 09:00:00.000
    8           2020-04-05 10:00:00.000

Here’s the results I need:

ItemNumber  |      DateOrdered
    5           2020-04-01 10:00:00.000
    6           2020-04-02 10:00:00.000
    2           2020-04-03 10:00:00.000
    5           2020-04-04 10:00:00.000
    8           2020-04-05 10:00:00.000

This is as close as I can get with it:

with tempTable as
(
  select
    *,
    row_number() over(partition by datediff(d, 0, DateOrdered) order by DateOrdered desc) as rn 
  from myTable
)
select *
from tempTable  
where rn = 1

Answer

You are almost there. You just need to fix the definition of your partition so it puts together all rows that belong to the same day.

This should do it:

with tempTable as
(
  select
    *,
    row_number() over(partition by cast(DateOrdered as date) order by DateOrdered desc) as rn 
  from myTable
)
select *
from tempTable  
where rn = 1

Advertisement

Answer