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

[PaxMagister]

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?

[rski]

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?

[PaxMagister]

sorry

I want to return

name game score
steve chess 50
steve soccer 48
evan football 49
evan soccer 47

[rski]

If there are only 3 games you can use that simple query, imagine the table name is test

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

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

[PaxMagister]

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.

[rski]

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

select name,game,score from (
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

rank<=...
 

Is that simple for you?