Select X of of Y in Aggregation - comp.databases.mysql

Message from discussion Select X of of Y in Aggregation

strawberry

View profile

More options 26 Feb 2007, 13:51

Newsgroups: comp.databases.mysql

From: "strawberry" <zac.ca...@gmail.com>

Date: 26 Feb 2007 05:51:31 -0800

Local: Mon 26 Feb 2007 13:51

Subject: Re: Select X of of Y in Aggregation

  Reply to author
  |
  Forward | Print | View thread
  | Show original
  | Report this message | Find messages by this author
  

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

Reply to author Forward