Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Unanswered: Order by - query highest 5

    I need to "List all details of the 5 highest prize money, descending order by money." I ran this query first:

    select * from prize
    order by money desc;

    Then, seeing the 5th highest figure was >60, I ran this query:

    select * from prize
    where money > 60.00
    order by money desc;

    How can I combine these two into one statement?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    MySQL includes a nice feature called LIMIT which can be included in your SQL statement. This effectively limits the number of rows returned:

    Code:
    SELECT *
    FROM   prize
    ORDER  BY money DESC
    LIMIT  5;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    order by - query highest 5

    Thanks for this, Ronan.

    However, returning 5 rows isn't the correct answer because there are two records which have the same prize amount. The answer should display 6 rows, and I know LIMIT 6 would do that, but is there a statement that will return the 5 highest prizes without knowing how many records this covers? Eg, what if there were 25 records which all fall into the top 5 prize amounts?
    In other words, do I need to look at the data to work out how many records should be returned in my query before being able to create this query, or can I create a query which will do it all for me?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT this.money 
      FROM prize AS this
    LEFT OUTER
      JOIN prize as that
        ON that.money > this.money
    GROUP
        BY this.money
    HAVING COUNT(that.money) < 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    order by - query highest 5

    Thank you.
    Unfortunately this returns four rows.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by melgra70 View Post
    Thank you.
    Unfortunately this returns four rows.
    yeah, you're right, i didn't have enough information

    could you please help me out and do a SHOW CREATE TABLE

    i will fix the query accordingly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    It is possible to include a LIMIT into a SELECT statement so long as it is not a subquery. So you could do this:

    Code:
    SELECT this.money
    FROM   prize AS this,
           (SELECT DISTINCT money
            FROM   prize
            ORDER  BY money DESC
            LIMIT  5) AS that
    WHERE  this.money = that.money;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    order by - query highest 5

    Many thanks, Rudi and Rowan.
    Rowan's query works.

  9. #9
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Actually it is Ronan but never mind glad the query works.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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