1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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