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

Expand|Select|Wrap|Line Numbers

 
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

 
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

Expand|Select|Wrap|Line Numbers

 
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

Expand|Select|Wrap|Line Numbers

rank<=...

Is that simple for you?

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

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

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

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

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

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

Similar PostgreSQL Database bytes

/bytes/development

/bytes/it

/bytes/about