pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedA SQL Question About distinct, limit, group by, having, aggregate
5+ messages / 4 participants
[nested] [flat]
* A SQL Question About distinct, limit, group by, having, aggregate
@ 2005-03-31 02:29 ªL©[¾N <s9154083@cyut.edu.tw>
2005-03-31 03:01 ` Re: A SQL Question About distinct, limit, group by, having, aggregate Jaime Casanova <systemguards@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: ªL©[¾N @ 2005-03-31 02:29 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: A SQL Question About distinct, limit, group by, having, aggregate
2005-03-31 02:29 A SQL Question About distinct, limit, group by, having, aggregate ªL©[¾N <s9154083@cyut.edu.tw>
@ 2005-03-31 03:01 ` Jaime Casanova <systemguards@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jaime Casanova @ 2005-03-31 03:01 UTC (permalink / raw)
To: 林�[鋅 <s9154083@cyut.edu.tw>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: A SQL Question About distinct, limit, group by, having, aggregate
@ 2005-03-31 05:14 Lin Kun Hsin <s9154083@cyut.edu.tw>
2005-03-31 08:05 ` Re: A SQL Question About distinct, limit, group by, having, Ragnar Hafstað <gnari@simnet.is>
0 siblings, 1 reply; 5+ messages in thread
From: Lin Kun Hsin @ 2005-03-31 05:14 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: A SQL Question About distinct, limit, group by, having,
2005-03-31 05:14 Re: A SQL Question About distinct, limit, group by, having, aggregate Lin Kun Hsin <s9154083@cyut.edu.tw>
@ 2005-03-31 08:05 ` Ragnar Hafstað <gnari@simnet.is>
0 siblings, 0 replies; 5+ messages in thread
From: Ragnar Hafstað @ 2005-03-31 08:05 UTC (permalink / raw)
To: Lin Kun Hsin <s9154083@cyut.edu.tw>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: A SQL Question About distinct, limit, group by, having, aggregate
@ 2005-03-31 12:24 Lin Kun Hsin <s9154083@cyut.edu.tw>
0 siblings, 0 replies; 5+ messages in thread
From: Lin Kun Hsin @ 2005-03-31 12:24 UTC (permalink / raw)
To: pgsql-sql
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 from allscore 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)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-03-31 12:24 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-31 02:29 A SQL Question About distinct, limit, group by, having, aggregate ªL©[¾N <s9154083@cyut.edu.tw>
2005-03-31 03:01 ` Jaime Casanova <systemguards@gmail.com>
2005-03-31 05:14 Re: A SQL Question About distinct, limit, group by, having, aggregate Lin Kun Hsin <s9154083@cyut.edu.tw>
2005-03-31 08:05 ` Re: A SQL Question About distinct, limit, group by, having, Ragnar Hafstað <gnari@simnet.is>
2005-03-31 12:24 Re: A SQL Question About distinct, limit, group by, having, aggregate Lin Kun Hsin <s9154083@cyut.edu.tw>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox