Posted by Stephen2 on 07/11/07 12:08

On Jul 11, 12:51 pm, bonjella <amykim...@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

Is this what you mean

SELECT
u1.user_id,
(SELECT TOP 4 skill
FROM tbl_skill_scores u2
WHERE u2.user_id = u1.user_id
ORDER BY u2.score DESC)
FROM tbl_skill_scores u1
GROUP BY user_id

??

[Back to original message]