Re: A SQL Question About distinct, limit, group by, having, aggregate

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)