Help with a query

Hi,
I have a table tScores with 3 fields: user, score and stage.
What I want is to select the 2 best scores (highest) of each user.
Ex.
tScores
--
A, 20, 1
A, 10, 2
A, 30, 3
A, 40, 4
A, 10, 5
B, 20, 1
B, 30, 2
B, 10, 3
C, 10, 1
The result would be:
A, 40, 4
A, 30, 3
B, 30, 2
B, 20, 1
C, 10, 1
Thanks in advance,
John LewisJohn Lewis wrote:
> Hi,
>
> I have a table tScores with 3 fields: user, score and stage.
> What I want is to select the 2 best scores (highest) of each user.
>
> Ex.
> tScores
> --
> A, 20, 1
> A, 10, 2
> A, 30, 3
> A, 40, 4
> A, 10, 5
> B, 20, 1
> B, 30, 2
> B, 10, 3
> C, 10, 1
>
> The result would be:
>
> A, 40, 4
> A, 30, 3
> B, 30, 2
> B, 20, 1
> C, 10, 1
>
> Thanks in advance,
>
> John Lewis
How do you want to handle the case where the user has the same highest
score more than once (or more than twice)? I'll assume you want all
tied scores even if that means you return more than two rows per user:
SELECT [user], score, stage
FROM tScores AS S
WHERE 2 >
(SELECT COUNT(*)
FROM tScores
WHERE [user] = S.[user]
AND score > S.score) ;
User is a bad choice for a column name because it's a reserved word and
because it is too vague (user_name, user_login, user_id ?)
David Portas
SQL Server MVP
--|||Hello David,
I'll offer this as an alternative for SqlServer 2005:
drop table #tests
go
create table #tests
(
[user] char(1) not null,
score tinyint not null,
stage tinyint not null
)
go
insert into #tests values('A', 20, 1)
insert into #tests values('A', 10, 2)
insert into #tests values('A', 30, 3)
insert into #tests values('A', 40, 4)
insert into #tests values('A', 10, 5)
insert into #tests values('B', 20, 1)
insert into #tests values('B', 30, 2)
insert into #tests values('B', 10, 3)
insert into #tests values('C', 10, 1)
go
;with tests([user],score,stage,rank) as
(
select [user],score,stage,
rank() over (partition by [user] order by score desc) as rank
from #tests
)
select [user],score,stage
from tests
where rank < 3
order by [user],score desc
go
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||"John Lewis" <julugu@.gmail.com> wrote in message
news:1132057809.298727.286240@.o13g2000cwo.googlegroups.com...
> Hi,
>
> I have a table tScores with 3 fields: user, score and stage.
> What I want is to select the 2 best scores (highest) of each user.
>
<snip />

> Thanks in advance,
>
> John Lewis
>
Multipost (over in microsoft.public.access.queries, under "Easy
query? Not to me.").
Sincerely,
Chris O.|||Sorry to post in another group, but I thought it was
sqlserver.queries.
Thanks to all for the answers.