Posted by Dan Guzman on 07/11/07 12:23

> 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@mailinator.com> wrote in message
news:1184155692.723558.41230@57g2000hsv.googlegroups.com...
> 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]