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

Databases Reporting with SQL Aggregate and Numeric Functions Getting Minimum and Maximum Values

Eylon Cohen
Eylon Cohen
4,779 Points

Getting 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
Greg Kaleka
39,021 Points

Not 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
Eylon Cohen
4,779 Points

Simple 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
Greg Kaleka
39,021 Points

Hi 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 DESC and LIMIT 1. I'll update my answer.

Eylon Cohen
Eylon Cohen
4,779 Points

Hi, 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.