From: | "Joel Burton" <joel(at)joelburton(dot)com> |
---|---|
To: | "Uros Gruber" <uros(at)sir-mag(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: LIMIT between some column |
Date: | 2002-05-19 15:21:14 |
Message-ID: | JGEPJNMCKODMDHGOBKDNGELNCOAA.joel@joelburton.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> here is some data for explanation.
>
> id | parent |
> 0 | |
> 1 | 0 |
> 2 | 0 |
> 3 | 0 |
> 4 | 0 |
> 5 | 1 |
> 6 | 1 |
> 7 | 1 |
> 8 | 1 |
> 9 | 2 |
> 10 | 2 |
> 11 | 3 |
> 12 | 4 |
> 13 | 4 |
> 14 | 4 |
> 15 | 4 |
> 16 | 4 |
>
> When i execute my query i get all ids from 5 to 16, but i
> want it to limit somehow that i get only ids,
> 5,6,7,9,10,11,12,13,14. I hope my problem is understandable.
> Do I have to use join on table itself or how.
Let's simplify your problem to the table above. To show just the first 3
rows (by id) for each parent:
create table limited (id serial primary key, parent int not null);
insert into limited (parent) values (0);
insert into limited (parent) values (0);
insert into limited (parent) values (0);
insert into limited (parent) values (0);
insert into limited (parent) values (1);
insert into limited (parent) values (1);
insert into limited (parent) values (1);
insert into limited (parent) values (1);
insert into limited (parent) values (2);
insert into limited (parent) values (2);
insert into limited (parent) values (3);
insert into limited (parent) values (4);
insert into limited (parent) values (4);
insert into limited (parent) values (4);
insert into limited (parent) values (4);
insert into limited (parent) values (4);
select id,
parent
from Limited as L0 where (select count(*)
from Limited as L1
where L0.parent=L1.parent
and L1.id < L0.id) < 3;
- J.
Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant
From | Date | Subject | |
---|---|---|---|
Next Message | Robert J. Sanford, Jr. | 2002-05-19 15:32:50 | Re: how to get id of last insert on a serial type? |
Previous Message | Neil Conway | 2002-05-19 14:31:36 | Re: sun solaris & postgres |