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

Getting the second value (rather than the first) for every group of rows with the same user_id

Hi, Is there an easy way to get the second value (rather than the first) for every group of rows with the same user_id?

I tried very hard to find an answer to this problem. Maybe it is still above my level. In that case, is there a course in which I might find an answer?

1 Answer

Steven Parker
Steven Parker
229,786 Points
Steven Parker
Steven Parker
229,786 Points

By "second value" I'm guessing you mean that instead of the maximum, you would like the value that is the second largest?

If so, you could use a sub-query to exclude the actual maximum from being part of the aggregate:

SELECT MAX(cost) AS "Second Largest Cost", user_id
   FROM orders o
   WHERE cost < (SELECT MAX(cost) FROM orders WHERE user_id = o.user_id)
   GROUP BY user_id;

Sub-queries are covered in the courses, but I don't think this particular use is shown as an example.

Eylon Cohen
Eylon Cohen
4,779 Points
Eylon Cohen
Eylon Cohen
4,779 Points

Yes, That is what I meant :) Thank you!

I hope to get to sub-queries soon to understand better. I do like ask, if it okey - when the function MAX is preformed on cost in the main SELECT, it is now refering the the second largest cost. What if I would like to add ANOTHER column, with the FIRST largest cost (the real maximum)?

Steven Parker
Steven Parker
229,786 Points
Steven Parker
Steven Parker
229,786 Points

That would be a bit more complicated. Right now, the main query never sees the lines with the actual maximum, because the WHERE clause is filtering them out. But you could do it with a JOIN operation and something called a "derived table". This will also be covered in the courses.

Eylon Cohen
Eylon Cohen
4,779 Points
Eylon Cohen
Eylon Cohen
4,779 Points

Thanks again Steven!