Re: LIMIT between some column

> 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