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

FAQ SQL FAQ
#1

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
FAQ: Aggregate Functions - Max / Min
#2

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

10 Likes
#3

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
#4

This is so useful, thank you!

#5

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