Обсуждение: LIMIT between some column
Hi! My query is SELECT id_category,name FROM categories WHERE level BETWEEN 1 AND 2 AND lft>1 AND rgt<100 AND active=true ORDER BY name This is about 30 or more records. This records can be grouped by id_parent. What i want to do is limit data in a way that i could get for example only 3 or 4 records with same id_parent. 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. -- lp, Uros mailto:uros@sir-mag.com
> 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
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
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; > -----Original Message----- > From: Uros Gruber [mailto:uros@sir-mag.com] > Sent: Sunday, May 19, 2002 11:34 AM > To: Joel Burton > Cc: Uros Gruber; pgsql-general@postgresql.org > Subject: Re[2]: [GENERAL] LIMIT between some column > > 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. You don't need IDs per se, but for this to work, you do need _something_ you can order these by -- a datetime or money amount or something (would your "name" column do?) -- otherwise, how are you choosing which are the top 3 that you want to see? Randomly? As for speed: yep, it's slow. Correlated subqueries always are. An index on parent will help a lot, as will an index on whatever the comparison field is (id in my example). Or, possibly, someone else may be able to suggest a faster way, but I'm not seeing one right now. > especialy if i move some categories or delete any. This > method don't work anymore. Don't understand this point at all. What are you talking about? - J. Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant
Hi! Sunday, May 19, 2002, 5:47:08 PM, you wrote: >> >> 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. JB> You don't need IDs per se, but for this to work, you do need _something_ you JB> can order these by -- a datetime or money amount or something (would your JB> "name" column do?) -- otherwise, how are you choosing which are the top 3 JB> that you want to see? Randomly? I have some colum name on which i want to order. JB> As for speed: yep, it's slow. Correlated subqueries always are. An index on JB> parent will help a lot, as will an index on whatever the comparison field is JB> (id in my example). Or, possibly, someone else may be able to suggest a JB> faster way, but I'm not seeing one right now. I try to use indexes but it's still to slow. I'm faster if i select all and then cut those rows in PHP. >> especialy if i move some categories or delete any. This >> method don't work anymore. bye Uros