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?

22 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.

80 Likes
#4

This is so useful, thank you!

1 Like
#5

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

5 Likes
#6

Why if I use SELECT MIN(price), MAX(price), name FROM fake_apps; It only returns the name of the last function? Is there a rule or something similar in SQL for functions? Thanks

#7

It will appear with two headers, but I just listed each statement one after the next:

SELECT id, name, category, downloads, max(price)
FROM fake_apps;
SELECT id, name, category, downloads, min(price)
FROM fake_apps;

Other than that, I’m not sure how to do it and would rely on someone else to answer this question.

#8

The aggregate functions take multiple values/rows from the database and return a single value. It would find the row that had the maximum price and return the name for it. If the functions were flipped around, you would get the name for the min(price) value. I hope that makes some sense.

#9

The answer you have is very close. You could use this:

SELECT MIN(price), name 
FROM fake_apps
union
SELECT MAX(price), name 
FROM fake_apps;
1 Like
#10

Hi
i think if you run a query with the MAX( ) or MIN ( ) commands you just get the value, you don’t get the identification of any row.

maybe you are talking about the internal logic of the function it self?