Results 1 to 4 of 4
  1. #1
    Join Date
    May 2008
    Posts
    6

    Unanswered: Selecting things greater than the max of something else

    Let's pretend I have this table

    PHP Code:
    id|name|price|type|
    ------------------- 
    I need to select the name of all of the items that have a price greater than the max price of all items of a certain type.

    So, if I have 20 items in the table and 4 of them are of type 'A' and the max price for any item of type 'A' is 10, I need to select all items of the 20 with a price greater than 10.

    I tried joining the table with itself, but that didn't work so well. Can anyone tell me how to do this?

    Thank you.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is a classic homework assignment, isn't it?

    okay, can you write the query to find the largest price for type A items?

    show me that, and i'll show you how to use it in a subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2008
    Posts
    6
    SELECT MAX(price) AS maxPrice FROM table WHERE type = 'A';

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, great

    now here's how to return the names of all items that have a higher price --

    Code:
    SELECT name
      FROM table
     WHERE price >
           ( SELECT MAX(price) AS maxPrice
               FROM table
              WHERE type = 'A' )
    make sense?

    note that the maxPrice alias isn't actually needed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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