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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
JB> Knowledge Management & Technology Consultant