> 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@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant
