A have a table, a classic student test result table that contains
StudentID, TestID and Score. For any student there can be any number
of scores, including zero, for any given test. For a given student I
want to extract the scores for each test and am using:

Select * from Results where StudentId=x order by TestID, Score;

This returns all the results for a given student and works fine. What
I really want to do though is to only return the top five scores for
_each_ test.

I am sure this is easy and am guessing that this requires some form of
select from (select from ...) syntax but cannot work out what.

Could some kind soul please point me in the right direction?

Andrew

On 18 Apr, 09:02, "andrew_d_...@hotmail.com"
<andrew_d_...@hotmail.com> wrote:
> A have a table, a classic student test result table that contains
> StudentID, TestID and Score. For any student there can be any number
> of scores, including zero, for any given test. For a given student I
> want to extract the scores for each test and am using:
>
> Select * from Results where StudentId=x order by TestID, Score;
>
> This returns all the results for a given student and works fine. What
> I really want to do though is to only return the top five scores for
> _each_ test.
>
> I am sure this is easy and am guessing that this requires some form of
> select from (select from ...) syntax but cannot work out what.
>
> Could some kind soul please point me in the right direction?
>
> Andrew

There are just SO MANY answers around to this already!
I just Googled "mysql top n group" and got a massive list.
Here's a link to the first one:

http://thenoyes.com/littlenoise/?p=36

On 18 Apr, 09:02, "andrew_d_...@hotmail.com"
<andrew_d_...@hotmail.com> wrote:
> A have a table, a classic student test result table that contains
> StudentID, TestID and Score. For any student there can be any number
> of scores, including zero, for any given test. For a given student I
> want to extract the scores for each test and am using:
>
> Select * from Results where StudentId=x order by TestID, Score;
>
> This returns all the results for a given student and works fine. What
> I really want to do though is to only return the top five scores for
> _each_ test.
>
> I am sure this is easy and am guessing that this requires some form of
> select from (select from ...) syntax but cannot work out what.
>
> Could some kind soul please point me in the right direction?
>
> Andrew

There are just SO MANY answers around to this already!
I just Googled "mysql top n group" and got a massive list.
Here's a link to the first one:

http://thenoyes.com/littlenoise/?p=36

On 18 Apr, 16:27, Captain Paralytic <paul_laut...@yahoo.com> wrote:

> There are just SO MANY answers around to this already!
> I just Googled "mysql top n group" and got a massive list.
> Here's a link to the first one:
>
> http://thenoyes.com/littlenoise/?p=36

Thank you. That was the pointer that I needed. I was missing the
'group' in the search terms.

Andrew