SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Don't know how to make this query

    I don't know much about Mysql and can't figure how I would achieve the following. I want to select the 50 most expensive products, and I want to order them alphabetically. The following orders them by prize, but I don't know how to grab those results and order them by something else (alphabetically).
    Code:
    SELECT * FROM `products` ORDER BY `prize` DESC LIMIT 50;

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this. Would test it myself but I'm at work and we don't use MySQL here

    Code sql:
    SELECT *
      FROM ( SELECT *
               FROM products
             ORDER
                 BY price DESC
              LIMIT 50 )
    ORDER
        BY something_else;

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And, for what I've read so far about mysql this is a subquery, right? I thought subqueries were only supposed to return one result. I'm confused about that.

    Forgive my ignorance

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by mooomo View Post
    I thought subqueries were only supposed to return one result
    No, subqueries return a result set like any other query. This set can be empty, or contain one or more rows.
    Why did you think that?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    actually, a subquery in the SELECT clause may return only one row consisting of one column -- i.e. a singular scalar value, which is why it's called a scalar subquery

    elsewhere (e.g. in the FROM clause, or in an IN list or EXISTS predicate) a subquery can return multiple rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot for the help. That's what was getting me confused. Subquries can really get quite complicated.

    If someone knows of some good resources to learn about subqueries, I'll greatly appreaciate if they could link to them. I've already read the official mysql documentation.


Bookmarks

Posting Permissions

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