Go to Google Groups Home    comp.databases.mysql
Re: Select X of of Y in Aggregation

strawberry <zac.ca...@gmail.com>

On Feb 26, 4:39 am, "howa" <howac...@gmail.com> wrote:

> Consider a table like:

> user_id      score
>       1      90
>       1      80
>       1      70
>       2      85
>       2      80
>       2      80
>       .
>       .

> I want to select te average score of a user and will use sth
> like:

> SELECT avg( score )
> FROM `score`
> GROUP BY user_id

> but sometimes, i want to select the average, but dropping the lowest N
> score, how
> to write the query?

> thanks

Well here's a pretty substantial clue. Note that I've added the
primary key 'record_id' to the table to deal with ties.

SELECT count( * ) rank, a.user_id, A.score
FROM score A
JOIN score B ON ( A.user_id = B.user_id
AND A.score = B.score
AND A.record_id <= B.record_id )
OR (
A.user_id = B.user_id
AND A.score < B.score
)
GROUP BY A.user_id, A.record_id
HAVING rank <=2
LIMIT 0 , 30