Salman Salman - 3 months ago 17
SQL Question

Finding last record in a day

Please look at this result table, I want to retrieve last

Invoice_Number
of a
sales_rep
on
31-dec-2013


INVOICE_NUMBER INVOICE_DATE FK_SALES_REP_ID
519740000010026 12/31/13 10:14 AM 121
518210000010868 12/31/13 7:34 AM 91
519740000010050 12/31/13 2:29 PM 121
519920000010273 12/31/13 10:25 AM 94
514990000010269 12/31/13 10:51 AM 127
514990000010270 12/31/13 11:09 AM 127
505700000012330 12/31/13 12:12 PM 106
518210000010867 12/31/13 7:24 AM 91


Result Table :

INVOICE_NUMBER INVOICE_DATE FK_SALES_REP_ID
518210000010868 12/31/13 7:34 AM 91
519920000010273 12/31/13 10:25 AM 94
519740000010050 12/31/13 2:29 PM 121
505700000012330 12/31/13 12:12 PM 106
514990000010270 12/31/13 11:09 AM 127

Answer Source

As you didn't specify your DBMS this is Standard SQL:

select *
from
 (
   select t.*,
      row_number()
      over (partition by FK_SALES_REP_ID 
            order by INVOICE_DATE desc) as rn
   from tab as t
   where cast(INVOICE_DATE as date) = '12/31/13'
 ) as dt
where rn = 1