Selecting things greater than the max of something else

_89 · #1 (**permalink**) 10-19-08, 19:16

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.

r937 · #2 (**permalink**) 10-19-08, 21:11

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

_89 · #3 (**permalink**) 10-19-08, 21:12

SELECT MAX(price) AS maxPrice FROM table WHERE type = 'A';

r937 · #4 (**permalink**) 10-20-08, 00:07

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

Internet Services

Web Development

Digital Marketing

Consumer Tech