How to select latest date record

boybles · #1 (**permalink**) 12-28-11, 03:56

I'm trying to select the latest record before the provided date. For example, in the following scenario, I have a Tickets table:

Tickets
------------
TicketID (NUMBER 3,PK)
TicketDate (DATE)
EventName (VC2 50)

Example Data:
TicketID|TicketDate|Eventname
========================
1|1/3/2011|Concert A
2|5/4/2011|Concert B
3|6/3/2011|Concert C
4|7/5/2011|Concert D

In the above table, if I have a date of 6/1/2011, how do I select the latest record before that date (in this case, it would be TicketID=2)?

Thanks,
Tony

Littlefoot · #2 (**permalink**) 12-28-11, 05:45

Code:

SQL> select t.eventname
  2  from tickets t
  3  where t.ticketdate = (select max(t1.ticketdate)
  4                        from tickets t1
  5                        where t1.ticketdate < to_date('6/1/2011', 'mm/dd/yyyy')
  6                       );

EVENTNAME
--------------------------------------------------
Concert B

SQL>

shammat · #3 (**permalink**) 12-28-11, 08:02

If the table is large, using a window (aka analytical) function might be faster, because only a single scan is required:

Code:

select TicketID, 
       TicketDate, 
       EventName 
from (
    select TicketID, 
           TicketDate, 
           EventName, 
           dense_rank() over (order by TicketDate desc) as rnk
    from tickets
    where ticketdate < to_date('6/1/2011', 'mm/dd/yyyy')
) t
where rnk = 1;

boybles · #4 (**permalink**) 01-01-12, 18:05

Thank you guys. Both solutions worked beautifully.
Tony

Internet Services

Web Development

Digital Marketing

Consumer Tech