Sorting top three from any number 1

[mancroft]

Sorting top three from any number

I am doing a quiz.

A player can have as many goes as they like e.g. Fred has 6 goes, Jessica has 5 goes, Ann has 4 goes and they score:

Fred
3
6
3
7
2
4
Jessica
5
4
6
7
3
Ann
3
4
6
5

How do I sort the data alpahbetically and by number so that ONLY the top three scores for each player are shown:

Fred 7
Jessica 7
Ann 6
Fred 6
Jessica 6
Ann 5
Jessica 5
Ann 4
Fred 4


Thank you.

 

[TonyGroves]

If you show us some of your work so far, somebody might be able to help.

 

[TamedTech]

It would also be helpful to know what server side code your using .. PHP, ASP, ColdFusion?

Rob

 

[mancroft]

Never mind. I chose another route.

 

[Kozusnik]

Alright, I was going to suggest a descending sort with a limit of 3. Hope you found a answer.

Mark

SELECT * FROM management WHERE clue > 1
> 0 rows returned

--ThinkGeek T-Shrit

 

[guelphdad]

That would only give you the top three scores overall, not what the original query had in mind.

 

[r937]

select t1.name
     , t1.go
  from daTable as t1
inner
  join daTable as t2
    on t1.name = t2.name
   and t1.go  <= t2.go
group
    by t1.name
     , t1.go
having count(*) <= 3
order
    by t1.go desc
     , t1.name asc

whatever other route you took, it probably involves a lot of application-side code, right? well, with this query, you'll need no code at all, eh




r937.com | rudy.ca

 

[mancroft]

whatever other route you took, it probably involves a lot of application-side code, right?

True.

with this query, you'll need no code at all, eh

Cor, you are clever, Rudy.