Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: Finding the n.th higest value

    Hi.

    I have a table, "Message", that contains information about several messages. On field in the table, "no_views" shows how many times a message has been viewed.

    I want to be able to show the top three messages with the most views. How can this be done in sql?

    A query against a table like this
    MessageID | No_Views
    1 | 10
    2 | 110
    3 | 200
    4 | 150
    5 | 5

    should produce this output:
    3 | 200
    4 | 150
    2 | 110

    (The order is not important).

    Thanks for your help!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select MessageID
         , No_Views
      from Message M
     where 3 > (
       select count(*)
         from Message 
         where No_Views > M.No_Views )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    2
    Perfect!

    Thanks for a very smart solution :-)

  4. #4
    Join Date
    Feb 2004
    Posts
    41
    Rudy,
    ok this is for n maximum values, if i wnat to print only nth max, say if i want 1st, 2nd , 3rd,.. max value.

    how to do it in sql.
    'A candle will loose nothing by lighting an another candle'

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you want only the nth entry, use n-1 in the query

    for example, to find the 13th highest entry,
    Code:
    select MessageID
         , No_Views
      from Message M
     where 12 = (
       select count(*)
         from Message 
         where No_Views > M.No_Views )
    in english, the query finds the row where there are 12 rows with a higher value, so that must be the row with the 13th highest value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Posts
    41
    Ok. Thank u. Actully i tried it out and got the solution.
    'A candle will loose nothing by lighting an another candle'

Posting Permissions

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