If multiple rows have the minimum or maximum value, which one is returned when using MAX/MIN?

Question

If multiple rows have the minimum or maximum value, which one is returned when using MAX/MIN?

Answer

Typically, when you have more than one row that contains the minimum or maximum value in a column, the topmost row containing that value will be returned in the result.

For example, if the table contained multiple rows with the minimum price of 0.0, then the result of a query with MIN(price) will choose the topmost row from the table that had this price value.

Example

/* 
This should return the siliconphase app, because
it was the topmost row that had the minimum price 
value of the column. 
*/
SELECT id, name, MIN(price)
FROM fake_apps;
12 Likes

How can you make the output show all rows that contain the maximum price, instead of just top one?

10 Likes

You would need to use subqueries to do this. For example:

SELECT name,price FROM fake_apps
WHERE price = (SELECT max(price) FROM fake_apps);

So rather than SELECT-ing rows from the table where the price is exactly some number we specify, we compare price to the result of another query, in this case a MAX query.

61 Likes

This is so useful, thank you!

with out using max and min
select * from fake_apps order by price desc limit 1;