SQL – select greatest value Feb16 '05

# (1 of 2): Fuul » fluidnature.com

5 months, 2 weeks after the fact. (Sun 31 Jul 2005, 10:45 PM CST)

I am not sure what version of SQL you are using, but in T-SQl, you use the "MAX()" function, like this:

SELECT max(myColumn) myMaxValue FROM myTable

This grabs all the values in that column of the table and returns the row with the maximum value.

This works fine as is unless you also want other columns in the row at the same time, in which case you have to "group by" any column that is not being grouped together by a function like MAX (MAX is a "grouping" function.)

So if you had the following data, and wanted the most expensive fruit:

WierdTable
----------
ID, Type, Cost, Product
--------------------------
1, Book, 10.00, Why SQL sucks
2, Book, 15.00, Why SQL rules
3, Fruit, 12.00, Apple
4, Fruit, 2.00, Pear
5, Tool, 34.00, Wrench

You would have to use:

SELECT MAX(cost) maxcost, prodtype FROM wierdTable
WHERE prodtype = 'Fruit' GROUP BY prodtype

Which would return:

maxcost, prodtype
-------------
12.00, Fruit

of course, that doesn't tell you which row had the most, so if you want that, you have to join or sub-query back to the table again to get the entire row of the fruit that cost 12.00:

SELECT * FROM wierdTable
WHERE prodtype = 'Fruit'
AND cost IN
(SELECT MAX(cost) maxcost FROM wierdTable
WHERE prodtype = 'Fruit' GROUP BY prodtype)

# (2 of 2): Jason M.

2 years, 4 months after the fact. (Thu 05 Jul 2007, 11:16 PM CST)

Thank you so much for the "GROUP BY" tip -- I was making myself crazy with the MAX function. I just need to use the "GROUP BY" on the other columns.

Previous comment Return to entry