Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Question Unanswered: How to select latest date record

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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>

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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;

  4. #4
    Join Date
    Mar 2004
    Posts
    7

    Smile Thank you

    Thank you guys. Both solutions worked beautifully.
    Tony

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •