Re: LIMIT between some column

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(at)sir-mag(dot)com]
> Sent: Sunday, May 19, 2002 11:34 AM
> To: Joel Burton
> Cc: Uros Gruber; pgsql-general(at)postgresql(dot)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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant