Posted by Roy Harvey on 07/11/07 12:05

This answer uses a proprietary extension (TOP) only available in
Microsoft SQL Server. You could get this answer in this group or in
microsoft.public.sqlserver.programming.

SELECT *
FROM tbl_skill_scores as A
WHERE skill IN
(select TOP 4 skill
from tbl_skill_scores as B
where A.user_id = B.user_id
order by B.score DESC)

This answer does not use proprietary SQL as far as I know.

SELECT *
FROM tbl_skill_scores as A
WHERE (select count(*)
from tbl_skill_scores as B
where A.user_id = B.user_id
and A.score <= B.score) <= 4

Roy Harvey
Beacon Falls, CT

On Wed, 11 Jul 2007 11:51:49 -0000, bonjella <amykimber@gmail.com>
wrote:

>Hello,
>
> I have a table called tbl_skill_scores
>
>user_id - primary
>skill - varchar
>score - int
>
>Each user has 6 entries in this table, one for each skill.
>
>I'm trying to write sql that will return me each user's best 4 skills
>- i.e. the 4 out of the 6 that they are best at, so user 1 will have
>skills ABCD but user 2 could have BCDE.
>
>Once I have that I need to average them, but that shouldn't be too
>hard - it's getting each user's top 4 scores that I'm stumped on.
>
>can anyone nudge me in the right direction?
>
>Many thanks,
>
>Amy
>
>p.s. this is a sql question that happens to be about my ms-sql
>database, should I post this query here as I have done, or on
>comp.databases as it could be considered a more 'general' sql
>question? - A

[Back to original message]