Infinitive Posted January 31, 2011 Share Posted January 31, 2011 I don't normally need SQL help but this one's a bit beyond me and I'm hoping someone knows a trick. I have a table which can have users on it several times, along with scores, and I need to find everyone who's been on there at least twice along with how many times they appear and their second-highest score. And it's that underlined bit that's the problem. Consider some sample data: Scores_Table: ListingID User Version Score 1 Adam 1 500 2 Adam 2 600 3 Adam 3 700 4 Barry 1 525 5 Barry 2 625 6 Carla 1 550 7 Doug 1 575 7 Doug 2 675 And the goal would be to return User Appearances Second High Adam 3 600 Barry 2 525 Doug 2 575 So I start with SELECT User, Count(ListingID) as Appearances FROM Scores_Table GROUP BY User HAVING Appearances >= 2 Which is sufficient to get the number of appearances but not the second highest score. Min works for exactly two but not for three or more appearances. The usual technique for second highest on a table won't work unless I want to run it for every user (which is my fallback plan). So I'm hoping someone has a trick. Help appreciated, thanks. Quote Link to comment https://forums.phpfreaks.com/topic/226251-query-help-second-best-in-each-group/ Share on other sites More sharing options...
fenway Posted February 11, 2011 Share Posted February 11, 2011 You'll need to get back to top N for each group, and then just throw out the first one. Quote Link to comment https://forums.phpfreaks.com/topic/226251-query-help-second-best-in-each-group/#findComment-1172733 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.