SELECT second highest date

Discussion in 'MySQL' started by KingCobra, Aug 9, 2010.

0
  1. #1
    table name: date
    fiels name: news_date

    news_date
    -----------
    2009-12-11
    2009-12-31
    2009-12-31
    2010-01-01
    2010-01-01
    2010-02-01
    2010-02-01
    2010-02-03
    2010-02-03
    2010-02-03

    I want to run a query that will result second highest 'news_date'.
    That means '2010-02-01' will produce (only one date/row & not all row of same date)

    Would you please tell me what will be the Query?
     
    KingCobra, Aug 9, 2010 IP
  2. gikone

    gikone Member

    Messages:
    72
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    45
    #2
    Simple as pie!
    
SELECT MAX(news_date) FROM date WHERE news_date NOT IN (SELECT MAX(news_date) FROM date)
    Code (markup):
    or
    
SELECT MAX(news_date) FROM ( SELECT news_date FROM date MINUS SELECT MAX(news_date) FROM date )
    Code (markup):
     
    gikone, Aug 9, 2010 IP
  3. koko5

    koko5 Active Member

    Messages:
    394
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    70
    #3
    date is sql data type and date() is a function, so it's not good idea to use this as a table name... However, simply:

    
SELECT DISTINCT news_date FROM `date` ORDER BY news_date DESC LIMIT 1,1;
    Code (markup):
     
    Last edited: Aug 9, 2010
    koko5, Aug 9, 2010 IP