Обсуждение: A SQL Question About distinct, limit, group by, having, aggregate

i have a little question, how to get 3 higher score student in every class.
Data looks like as below

problem:
id        class        score
john        a        100
jenny        a        70
ken        a        59
mary        b        85
jacky        b        80
lily        b        70
kevin        b        50
david        b        30
tina        c        85
tony        c        80
bare        c        70
vivian        c        60
eric        c        57
andy        c        50

result:
id        class        score
john        a        100
jenny        a        70
ken        a        59
mary        b        85
jacky        b        80
lily        b        70
tina        c        85
tony        c        80
bare        c        70

--
http://alumni.cyut.edu.tw
Open WebMail Project (http://openwebmail.org)

On Thu, 31 Mar 2005 10:29:16 +0800, "林�[鋅" <s9154083@cyut.edu.tw> wrote:
> i have a little question, how to get 3 higher score student in every class.
> Data looks like as below
>
> problem:
> id        class        score
> john        a        100
> jenny        a        70
> ken        a        59
> mary        b        85
> jacky        b        80
> lily        b        70
> kevin        b        50
> david        b        30
> tina        c        85
> tony        c        80
> bare        c        70
> vivian        c        60
> eric        c        57
> andy        c        50
>
> result:
> id        class        score
> john        a        100
> jenny        a        70
> ken        a        59
> mary        b        85
> jacky        b        80
> lily        b        70
> tina        c        85
> tony        c        80
> bare        c        70
>
Actually, i don't see any difference between problem and results but maybe

select * from yourTable order by class, score desc

regards,
Jaime Casanova

below is the sql schema. i hope it will help. 

i want the top 3 score students in every class

below is the original sql solution, but when we have 100 class , we have to
union 100 times?
have any better performance statement? 

select * from (
(select * from allscore where class = 'a' order by score desc limit 3)
union
(select * from allscore where class = 'b' order by score desc limit 3)
union
(select * from allscore where class = 'c' order by score desc limit 3)
) as t1 order by class,score desc


CREATE TABLE allscore (   id character varying(20) NOT NULL,   "class" character(1) NOT NULL,   score integer
);
insert into allscore(id, class, score) values ('john','a','100');
insert into allscore(id, class, score) values ('jenny','a','70');
insert into allscore(id, class, score) values ('ken','a','59');
insert into allscore(id, class, score) values ('mary','b','85');
insert into allscore(id, class, score) values ('jacky','b','80');
insert into allscore(id, class, score) values ('lily','b','70');
insert into allscore(id, class, score) values ('kevin','b','50');
insert into allscore(id, class, score) values ('david','b','30');
insert into allscore(id, class, score) values ('tina','c','85');
insert into allscore(id, class, score) values ('tony','c','80');
insert into allscore(id, class, score) values ('bare','c','70');
insert into allscore(id, class, score) values ('vivian','c','60');
insert into allscore(id, class, score) values ('eric','c','57');
insert into allscore(id, class, score) values ('andy','c','50');

--
http://alumni.cyut.edu.tw
Open WebMail Project (http://openwebmail.org)

On Thu, 2005-03-31 at 13:14 +0800, Lin Kun Hsin wrote:
> below is the sql schema. i hope it will help. 
> 
> i want the top 3 score students in every class

this has been discussed before. a quick google gives me:
http://archives.postgresql.org/pgsql-sql/2004-04/msg00067.php


gnari

Actually, i have a method to solve this problem.
But i really want to know, we have to write more statement to do one thing?


First step, we have to create 2 sequence. Let call them "foo" and "foo1".

create sequence foo;
create sequence foo1;

then, you can run below statement, and you will see the result that is we want.


select setval('foo',1);
select setval('foo1',1);
select id, score, class from (        select id, score, ph1.class , sequence1, sequence2, CASE WHEN ph1.cc > 5
THEN ph3.sequence + 4     ELSE ph3.sequence + ph1.cc - 1 END as tail from                 (select class, count(*) as cc
fromallscore group by class)         as ph1 join                     (select id, class, score, nextval('foo') as
sequence1,currval('foo') as
 
sequence2 from (select * from allscore order by class, score desc) as t2)        as ph2 on (ph1.class = ph2.class) join
           (select distinct on (class) class, nextval('foo1'), currval('foo1') as
 
sequence from (        select  id , score , class from allscore order by class, score desc) as t6)            as ph3 on
(ph2.class= ph3.class) order by ph1.class , score desc 
 
) as con where sequence2 <= tail;

--
http://alumni.cyut.edu.tw
Open WebMail Project (http://openwebmail.org)