Thread: 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)