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 trialEylon Cohen
4,779 PointsGetting 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
231,184 PointsBy "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
4,779 PointsEylon Cohen
4,779 PointsYes, 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
231,184 PointsSteven Parker
231,184 PointsThat 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 aJOIN
operation and something called a "derived table". This will also be covered in the courses.Eylon Cohen
4,779 PointsEylon Cohen
4,779 PointsThanks again Steven!