Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    26

    Thumbs up Unanswered: how to query second latest date??

    hi,

    i have a record with different dates

    created, num
    10/31/2003 2:48:48 PM , 123456
    1/7/2004 8:23:58 AM, 123456
    9/23/2004 8:16:26 PM, 123456
    9/13/2004 2:48:10 PM, 123456
    5/7/2004 10:30:57 AM, 123456


    SELECT MAX(created) AS recent, num
    FROM test
    GROUP BY num

    this will give: 9/23/2004 8:16:26 PM, 123456

    what is the sql to get: 9/13/2004 2:48:10 PM,123456

    ????

  2. #2
    Join Date
    Aug 2004
    Posts
    26
    note that i cant delete the latest record from that list, and then get the max date again, because some records have only one occurance (one date). that record will be lost.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Would this be OK?
    Code:
    SELECT created, num
    FROM (SELECT created, num, RANK() OVER (ORDER BY created DESC) rnk
          FROM your_table) tab1
    WHERE rnk = 2;

Posting Permissions

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