From: "Lin Kun Hsin" <s9154083@cyut.edu.tw>
To: "pgsql-sql" <pgsql-sql@postgresql.org>
Subject: Re: A SQL Question About distinct, limit, group by, having, aggregate
Date: Thu, 31 Mar 2005 13:14:40 +0800
Message-Id: <20050331050120.M45373@cyut.edu.tw>
MIME-Version: 1.0
Content-Type: text/plain;
	charset=big5

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)