top X scores for each user

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

Re: top X scores for each user

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
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

Re: top X scores for each user

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

??

Re: top X scores for each user

> 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

This query will fail when a user has more than one row tbl_skill_scores. A
Subquery used as an expression may return only zero or one row.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Stephen2" wrote in message
news:1184155692.723558.41230@57g2000hsv.googlegroups.com...
> On Jul 11, 12:51 pm, bonjella 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
>
> ??
>

Re: top X scores for each user

On Jul 11, 1:23 pm, "Dan Guzman" online.sbcglobal.net> wrote:
> > 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
>
> This query will fail when a user has more than one row tbl_skill_scores. A
> Subquery used as an expression may return only zero or one row.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Stephen2" wrote in message
>
> news:1184155692.723558.41230@57g2000hsv.googlegroups.com...
>
> > On Jul 11, 12:51 pm, bonjella 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
>
> > ??

Thanks, I realised that after I posted so I quickly removed my post. I
shouldn't have jumped in so quickly!

Re: top X scores for each user

On Jul 11, 1:05 pm, Roy Harvey wrote:

> 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)
>

Wonderful, that did the trick - thanks :-)

Amy