Message from discussion Select X of of Y in Aggregation

Path: g2news2.google.com!postnews.google.com!p10g2000cwp.googlegroups.com!not-for-mail
From: "strawberry" <zac.ca...@gmail.com>
Newsgroups: comp.databases.mysql
Subject: Re: Select X of of Y in Aggregation
Date: 26 Feb 2007 05:51:31 -0800
Organization: http://groups.google.com
Lines: 42
Message-ID: <1172497891.849821.177350@p10g2000cwp.googlegroups.com>
References: <1172464786.779436.114580@h3g2000cwc.googlegroups.com>
NNTP-Posting-Host: 81.179.24.92
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1172497897 13885 127.0.0.1 (26 Feb 2007 13:51:37 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 26 Feb 2007 13:51:37 +0000 (UTC)
In-Reply-To: <1172464786.779436.114580@h3g2000cwc.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.8.1.2) Gecko/20070219 Firefox/2.0.0.2,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: p10g2000cwp.googlegroups.com; posting-host=81.179.24.92;
   posting-account=Ol562Q0AAACVodGfuJS5t39NKZQJw8cO

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