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

On Jul 11, 1:23 pm, "Dan Guzman" <guzma...@nospam-
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" <Step...@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
>
> > ??

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

[Back to original message]