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

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

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