SELECTING a row by its highest value

[Phaelon]

Hey all,

 

I want to select a row by its highest value for a specific field (a field called 'price').

 

I tried it with the following command, but it failed:

 

SELECT * FROM products WHERE MAX(price) AND gender = 'Mens' AND type = 'T-Shirt';

 

This is my table:

 

+-----------------+--------------+------+-----+-------------------+-------+
| Field           | Type         | Null | Key | Default           | Extra |
+-----------------+--------------+------+-----+-------------------+-------+
| number          | int(10)      | YES  |     | NULL              |       |
| gender          | varchar(6)   | YES  |     | NULL              |       |
| type            | varchar(25)  | YES  |     | NULL              |       |
| name            | varchar(50)  | YES  |     | NULL              |       |
| brand           | varchar(50)  | YES  |     | NULL              |       |
| colour          | varchar(50)  | YES  |     | NULL              |       |
| material        | varchar(50)  | YES  |     | NULL              |       |
| countryoforigin | varchar(50)  | YES  |     | NULL              |       |
| price           | decimal(6,2) | YES  |     | NULL              |       |
| cost            | decimal(6,2) | YES  |     | NULL              |       |
| adddate         | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
| weight          | int(5)       | YES  |     | NULL              |       |
| sizetype        | varchar(25)  | YES  |     | NULL              |       |
| sizexs          | varchar(2)   | YES  |     | NULL              |       |
| sizes           | varchar(2)   | YES  |     | NULL              |       |
| sizem           | varchar(2)   | YES  |     | NULL              |       |
| sizel           | varchar(2)   | YES  |     | NULL              |       |
| sizexl          | varchar(2)   | YES  |     | NULL              |       |
+-----------------+--------------+------+-----+-------------------+-------+

 

Does anyone know why it failed?

[Barand]

SELECT * FROM products 

WHERE  gender = 'Mens' AND type = 'T-Shirt'

AND price = (

    SELECT MAX(price) 

    FROM products 

    WHERE  gender = 'Mens' AND type = 'T-Shirt'

    )

[Phaelon]

Thanks. Can you please explain to me why my way failed, so I don't have to ask again?

[Barand]

You want to know where the price = MAX(price).

 

The WHERE clause is a boolean expression and selects those records where it evaluates to TRUE. Your WHERE clause (WHERE MAX(price)) always evaluates to true unless the max price is 0 and so selects all records

 

edit: Because you use an aggregation function (MAX, MIN, SUM, COUNT etc) without a group by, you only get a single row returned.

Edited May 12, 2014 by Barand

[mac_gyver]

using an aggregate function in a where clause produces a query error (Invalid use of group function) because the function operates on values that are in the result set, but the where clause is what is selecting which rows are in the result set.