Re: LIMIT between some column

Hi,

I tried this and it works, but i don't like this. because
it's to slow and i have to use ids like i do it in example. I
want to have something that i'm not forced to use ids like
here.

especialy if i move some categories or delete any. This
method don't work anymore.


bye,
 Uros


Sunday, May 19, 2002, 5:21:14 PM, you wrote:

>> 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.

JB> Let's simplify your problem to the table above. To show just the first 3
JB> rows (by id) for each parent:

JB> create table limited (id serial primary key, parent int not null);

JB> insert into limited (parent) values (0);
JB> insert into limited (parent) values (0);
JB> insert into limited (parent) values (0);
JB> insert into limited (parent) values (0);
JB> insert into limited (parent) values (1);
JB> insert into limited (parent) values (1);
JB> insert into limited (parent) values (1);
JB> insert into limited (parent) values (1);
JB> insert into limited (parent) values (2);
JB> insert into limited (parent) values (2);
JB> insert into limited (parent) values (3);
JB> insert into limited (parent) values (4);
JB> insert into limited (parent) values (4);
JB> insert into limited (parent) values (4);
JB> insert into limited (parent) values (4);
JB> insert into limited (parent) values (4);

JB> select id,
JB>        parent
JB>   from Limited as L0 where (select count(*)
JB>                               from Limited as L1
JB>                              where L0.parent=L1.parent
JB>                                and L1.id < L0.id) < 3;

JB> - J.

JB> Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
JB> Knowledge Management & Technology Consultant