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

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)