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 20:24:03 +0800
Message-ID: <20050331121514.M9051@cyut.edu.tw> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: s9154083@cyut.edu.tw
Subject: Re: A SQL Question About distinct, limit, group by, having, aggregate
In-Reply-To: <20050331121514.M9051@cyut.edu.tw>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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