By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,515 Members | 1,363 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,515 IT Pros & Developers. It's quick & easy.

How can I limit a query to 2 of the same value in one col ordered by another col?

P: 3
Say I have a table:
name game score
Steve Chess 50
Steve Soccer 48
Steve Football 45
Evan Chess 46
Evan Soccer 47
Evan Football 49

I want to return each column for the two highest scores for each person. Can anyone tell me how to do this?
Jul 28 '09 #1
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 700
Gosh my English sucks, can you explain what do you want to achieve?
Wat do you want the query select from that example you give?
Jul 28 '09 #2

P: 3

I want to return

name game score
steve chess 50
steve soccer 48
evan football 49
evan soccer 47
Jul 28 '09 #3

Expert 100+
P: 700
If there are only 3 games you can use that simple query, imagine the table name is test
Expand|Select|Wrap|Line Numbers
  1. select * from test where (name,score) not in (select name,min(score) from test group by name);
The more complex solution that do not depend on number of games is
Expand|Select|Wrap|Line Numbers
  1. select * from test where (name,score) in (select name,max(score) from test group by name) or (name,score) in (select name,max(score) from test where (name,score) not in (select name,max(score) from test group by name) group by name);
I don't know what postgres version you use but to simplify the second query you can use 'with' statement
Jul 29 '09 #4

P: 3
Thanks for the response. I guess I should have been a little more vague in my question, or a little more wordy. That was actually a simplified example. What if I wanted to get the top 10, so if there were more games for each person? I guess I can just nest it more, but I was looking for a more flexible solution. Sorry for not being as clear with my first question. Again thanks for responding.
Jul 29 '09 #5

Expert 100+
P: 700
You want a simple solution?, I guess writting your own function is not a simple solution is it?
The simplest solution is
1) Upgrade database to 8.4 (unfortuantely it is still non stable engine)
2) Write a query
Expand|Select|Wrap|Line Numbers
  1. select name,game,score from (
  2. select name, game, score, rank() over(partition by name order by score desc) as rank from test)  foo where rank<=2
If you want more highest scores you only need to change the condition
Expand|Select|Wrap|Line Numbers
  1. rank<=...
Is that simple for you?
Jul 29 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.