Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Eylon Cohen
4,779 PointsGetting the primary key of the row with the maximum value
The course explains how to get the maximum value. I've be tring to get the id of the row, rather then the value. When I try to get the id, I get the first id from each group, not the id of the row with the maximum value.
SELECT table.id, MAX(table.value) FROM TABLE
GROUP BY table.value.
What am I missing?
Thank you!
1 Answer
Greg Kaleka
39,021 PointsNot sure what the problem is because it's working for me with this dataset. Can you show some actual examples that are returning unexpected results? Hopefully you're not actually grouping by the same column as you're MAXing, because that doesn't really make sense.
Here's an example that worked for me:
SELECT orders.id, MAX(orders.cost), orders.status FROM ORDERS
GROUP BY orders.status;
Edit: There are some cases where the above won't work. Here's a reliable way to get the data you're looking for:
SELECT id, status, cost FROM orders GROUP BY status ORDER BY cost DESC LIMIT 1;
That will order the results by cost from highest to lowest, and limit the results to 1, leaving only the max number. This is a better way to do it regardless - you're trying to get back the row with the max value, whereas MAX is an aggregate function. You should use it when you want to summarize data, not to select specific rows.
Eylon Cohen
4,779 PointsEylon Cohen
4,779 PointsSimple example: mysql> INSERT INTO table1 (id, value, a, b) VALUES (1,1,1,1), (2,2,1,1), (3,3,1,1), (4,1,2,1), (5,2,2,1), (6,1,2,2);
mysql> SELECT a,b,id, MAX(value) FROM table1 -> GROUP BY a AND b;
+------+------+----+------------+
| a | b | id | MAX(value) |
+------+------+----+------------+
| 1 | 1 | 1 | 3 |
+------+------+----+------------+
The max value is ok, but the rest does not fit...
Greg Kaleka
39,021 PointsGreg Kaleka
39,021 PointsHi Eylon,
OK I see more clearly what the problem is. You can't use MAX and GROUP BY like this. There are a few complex ways to handle this, but the easiest is to simply
ORDER BY value DESCandLIMIT 1. I'll update my answer.Eylon Cohen
4,779 PointsEylon Cohen
4,779 PointsHi, The problem is that I need the maximum from each group (in your example, I have several values in "status", not only one) . The lower solution only gives back the maximum of the entire table. the first solution is what I tried, but agian, it does give me the maximum cost for each statues, but the id is not correct and is not corelated with the cost presented.