top X scores for each user

#1

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

#2

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 (AT) gmail (DOT) com>
wrote:

Quote:

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

#3

On Jul 11, 12:51 pm, bonjella <amykim... (AT) gmail (DOT) com> wrote:

Quote:

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

??

#4

Quote:

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" <Stephen (AT) mailinator (DOT) com> wrote

Quote:

On Jul 11, 12:51 pm, bonjella <amykim... (AT) gmail (DOT) 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

??

#5

On Jul 11, 1:23 pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:

Quote:

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" <Step... (AT) mailinator (DOT) com> wrote in message

news:1184155692.723558.41230 (AT) 57g2000hsv (DOT) googlegroups.com...

On Jul 11, 12:51 pm, bonjella <amykim... (AT) gmail (DOT) 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

??

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