pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLIMIT BASED ON PERCENT
11+ messages / 5 participants
[nested] [flat]
* LIMIT BASED ON PERCENT
@ 2009-11-18 19:12 Another Trad <anothertrad@gmail.com>
2009-11-18 19:21 ` Re: LIMIT BASED ON PERCENT Lee Hachadoorian <lee.hachadoorian@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Another Trad @ 2009-11-18 19:12 UTC (permalink / raw)
To: pgsql-sql
My question is quite simple: I want to select all the records from my table,
but I want apply a LIMIT of 20% in the lines. like:
select * from client limit 20%
I have tried (of course, with no success) this:
select * from client limit ((select count(*) from client)*20/100)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT BASED ON PERCENT
2009-11-18 19:12 LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
@ 2009-11-18 19:21 ` Lee Hachadoorian <lee.hachadoorian@gmail.com>
2009-11-18 19:24 ` Re: LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Lee Hachadoorian @ 2009-11-18 19:21 UTC (permalink / raw)
To: Another Trad <anothertrad@gmail.com>; +Cc: pgsql-sql
Your SQL works for me exactly as it is (substituting a table in my
database). What error are you getting?
On Wed, Nov 18, 2009 at 2:12 PM, Another Trad <anothertrad@gmail.com> wrote:
> My question is quite simple: I want to select all the records from my table,
> but I want apply a LIMIT of 20% in the lines. like:
> select * from client limit 20%
> I have tried (of course, with no success) this:
> select * from client limit ((select count(*) from client)*20/100)
>
--
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT BASED ON PERCENT
2009-11-18 19:12 LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:21 ` Re: LIMIT BASED ON PERCENT Lee Hachadoorian <lee.hachadoorian@gmail.com>
@ 2009-11-18 19:24 ` Another Trad <anothertrad@gmail.com>
2009-11-18 19:27 ` Re: LIMIT BASED ON PERCENT Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 11+ messages in thread
From: Another Trad @ 2009-11-18 19:24 UTC (permalink / raw)
To: Lee Hachadoorian <lee.hachadoorian@gmail.com>; +Cc: pgsql-sql
No, It doesn't.
In my machine:
First select
ERROR: syntax error at end of input
LINE 1: select * from rapadura.cliente limit 20%
^
Second one:
ERROR: argument of LIMIT must not contain subqueries
Postgres 8.3
2009/11/18 Lee Hachadoorian <lee.hachadoorian@gmail.com>
> Your SQL works for me exactly as it is (substituting a table in my
> database). What error are you getting?
>
> On Wed, Nov 18, 2009 at 2:12 PM, Another Trad <anothertrad@gmail.com>
> wrote:
> > My question is quite simple: I want to select all the records from my
> table,
> > but I want apply a LIMIT of 20% in the lines. like:
> > select * from client limit 20%
> > I have tried (of course, with no success) this:
> > select * from client limit ((select count(*) from client)*20/100)
> >
>
>
>
> --
> Lee Hachadoorian
> PhD Student, Geography
> Program in Earth & Environmental Sciences
> CUNY Graduate Center
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT BASED ON PERCENT
2009-11-18 19:12 LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:21 ` Re: LIMIT BASED ON PERCENT Lee Hachadoorian <lee.hachadoorian@gmail.com>
2009-11-18 19:24 ` Re: LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
@ 2009-11-18 19:27 ` Guillaume Lelarge <guillaume@lelarge.info>
2009-11-18 19:28 ` Re: LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:30 ` Re: LIMIT BASED ON PERCENT Pavel Stehule <pavel.stehule@gmail.com>
2009-11-19 00:20 ` Re: LIMIT BASED ON PERCENT Kris Kewley <kris.kewley@gmail.com>
0 siblings, 3 replies; 11+ messages in thread
From: Guillaume Lelarge @ 2009-11-18 19:27 UTC (permalink / raw)
To: pgsql-sql; +Cc: Another Trad <anothertrad@gmail.com>; Lee Hachadoorian <lee.hachadoorian@gmail.com>
Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit :
> No, It doesn't.
> In my machine:
>
> First select
> ERROR: syntax error at end of input
> LINE 1: select * from rapadura.cliente limit 20%
> ^
> Second one:
> ERROR: argument of LIMIT must not contain subqueries
>
> Postgres 8.3
>
It works for Lee because obviously he's working on a 8.4 server. You can use
subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For earlier
releases, there's no way to do this in a single query.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT BASED ON PERCENT
2009-11-18 19:12 LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:21 ` Re: LIMIT BASED ON PERCENT Lee Hachadoorian <lee.hachadoorian@gmail.com>
2009-11-18 19:24 ` Re: LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:27 ` Re: LIMIT BASED ON PERCENT Guillaume Lelarge <guillaume@lelarge.info>
@ 2009-11-18 19:28 ` Another Trad <anothertrad@gmail.com>
2009-11-18 19:35 ` Re: LIMIT BASED ON PERCENT Pavel Stehule <pavel.stehule@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Another Trad @ 2009-11-18 19:28 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: pgsql-sql@postgresql.org, Lee Hachadoorian <lee.hachadoorian@gmail.com>
But there is any way to do it?
2009/11/18 Guillaume Lelarge <guillaume@lelarge.info>
> Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit :
> > No, It doesn't.
> > In my machine:
> >
> > First select
> > ERROR: syntax error at end of input
> > LINE 1: select * from rapadura.cliente limit 20%
> > ^
> > Second one:
> > ERROR: argument of LIMIT must not contain subqueries
> >
> > Postgres 8.3
> >
>
> It works for Lee because obviously he's working on a 8.4 server. You can
> use
> subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For
> earlier
> releases, there's no way to do this in a single query.
>
>
> --
> Guillaume.
> http://www.postgresqlfr.org
> http://dalibo.com
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT BASED ON PERCENT
2009-11-18 19:12 LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:21 ` Re: LIMIT BASED ON PERCENT Lee Hachadoorian <lee.hachadoorian@gmail.com>
2009-11-18 19:24 ` Re: LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:27 ` Re: LIMIT BASED ON PERCENT Guillaume Lelarge <guillaume@lelarge.info>
2009-11-18 19:28 ` Re: LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
@ 2009-11-18 19:35 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Pavel Stehule @ 2009-11-18 19:35 UTC (permalink / raw)
To: Another Trad <anothertrad@gmail.com>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; pgsql-sql@postgresql.org, Lee Hachadoorian <lee.hachadoorian@gmail.com>
2009/11/18 Another Trad <anothertrad@gmail.com>:
> But there is any way to do it?
CREATE OR REPLACE twenty()
RETURNS SETOF foo AS $$
DECLARE
rows int;
r record;
BEGIN
rows := (SELECT count(*) FROM foo);
FOR r IN EXECUTE 'SELECT * FROM r ORDER BY some col LIMIT ' || (rows
* 0.2)::int LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
this code should to work in 8.3
regards
Pavel Stehule
>
> 2009/11/18 Guillaume Lelarge <guillaume@lelarge.info>
>>
>> Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit :
>> > No, It doesn't.
>> > In my machine:
>> >
>> > First select
>> > ERROR: syntax error at end of input
>> > LINE 1: select * from rapadura.cliente limit 20%
>> > ^
>> > Second one:
>> > ERROR: argument of LIMIT must not contain subqueries
>> >
>> > Postgres 8.3
>> >
>>
>> It works for Lee because obviously he's working on a 8.4 server. You can
>> use
>> subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For
>> earlier
>> releases, there's no way to do this in a single query.
>>
>>
>> --
>> Guillaume.
>> http://www.postgresqlfr.org
>> http://dalibo.com
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT BASED ON PERCENT
2009-11-18 19:12 LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:21 ` Re: LIMIT BASED ON PERCENT Lee Hachadoorian <lee.hachadoorian@gmail.com>
2009-11-18 19:24 ` Re: LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:27 ` Re: LIMIT BASED ON PERCENT Guillaume Lelarge <guillaume@lelarge.info>
@ 2009-11-18 19:30 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-11-18 19:33 ` Re: LIMIT BASED ON PERCENT Lee Hachadoorian <lee.hachadoorian@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Pavel Stehule @ 2009-11-18 19:30 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: pgsql-sql@postgresql.org, Another Trad <anothertrad@gmail.com>; Lee Hachadoorian <lee.hachadoorian@gmail.com>
2009/11/18 Guillaume Lelarge <guillaume@lelarge.info>:
> Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit :
>> No, It doesn't.
>> In my machine:
>>
>> First select
>> ERROR: syntax error at end of input
>> LINE 1: select * from rapadura.cliente limit 20%
>> ^
>> Second one:
>> ERROR: argument of LIMIT must not contain subqueries
>>
>> Postgres 8.3
>>
>
> It works for Lee because obviously he's working on a 8.4 server. You can use
> subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For earlier
> releases, there's no way to do this in a single query.
>
yes, and don't use 20%.
select * from foo order by somecol limit (select (count(*)*0.2)::int from foo)
Regards
Pavel
>
> --
> Guillaume.
> http://www.postgresqlfr.org
> http://dalibo.com
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT BASED ON PERCENT
2009-11-18 19:12 LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:21 ` Re: LIMIT BASED ON PERCENT Lee Hachadoorian <lee.hachadoorian@gmail.com>
2009-11-18 19:24 ` Re: LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:27 ` Re: LIMIT BASED ON PERCENT Guillaume Lelarge <guillaume@lelarge.info>
2009-11-18 19:30 ` Re: LIMIT BASED ON PERCENT Pavel Stehule <pavel.stehule@gmail.com>
@ 2009-11-18 19:33 ` Lee Hachadoorian <lee.hachadoorian@gmail.com>
2009-11-18 19:39 ` Re: LIMIT BASED ON PERCENT Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Lee Hachadoorian @ 2009-11-18 19:33 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-sql
On Wed, Nov 18, 2009 at 2:30 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> yes, and don't use 20%.
>
> select * from foo order by somecol limit (select (count(*)*0.2)::int from foo)
>
> Regards
> Pavel
Is this faster on a large table? Because (select (count(*)*20/100)) worked fine.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT BASED ON PERCENT
2009-11-18 19:12 LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:21 ` Re: LIMIT BASED ON PERCENT Lee Hachadoorian <lee.hachadoorian@gmail.com>
2009-11-18 19:24 ` Re: LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:27 ` Re: LIMIT BASED ON PERCENT Guillaume Lelarge <guillaume@lelarge.info>
2009-11-18 19:30 ` Re: LIMIT BASED ON PERCENT Pavel Stehule <pavel.stehule@gmail.com>
2009-11-18 19:33 ` Re: LIMIT BASED ON PERCENT Lee Hachadoorian <lee.hachadoorian@gmail.com>
@ 2009-11-18 19:39 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Pavel Stehule @ 2009-11-18 19:39 UTC (permalink / raw)
To: Lee Hachadoorian <lee.hachadoorian@gmail.com>; +Cc: pgsql-sql
2009/11/18 Lee Hachadoorian <lee.hachadoorian@gmail.com>:
> On Wed, Nov 18, 2009 at 2:30 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> yes, and don't use 20%.
>>
>> select * from foo order by somecol limit (select (count(*)*0.2)::int from foo)
>>
>> Regards
>> Pavel
>
> Is this faster on a large table? Because (select (count(*)*20/100)) worked fine.
>
this is +/- same - 20/100 is maybe about 0.000001% faster - you don't
need one float to query cast, but this operation is only one pqr
query. The problem is two full scan of table.
Pavel
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT BASED ON PERCENT
2009-11-18 19:12 LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:21 ` Re: LIMIT BASED ON PERCENT Lee Hachadoorian <lee.hachadoorian@gmail.com>
2009-11-18 19:24 ` Re: LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:27 ` Re: LIMIT BASED ON PERCENT Guillaume Lelarge <guillaume@lelarge.info>
@ 2009-11-19 00:20 ` Kris Kewley <kris.kewley@gmail.com>
2009-11-19 06:47 ` Re: LIMIT BASED ON PERCENT Guillaume Lelarge <guillaume@lelarge.info>
2 siblings, 1 reply; 11+ messages in thread
From: Kris Kewley @ 2009-11-19 00:20 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: pgsql-sql; Another Trad <anothertrad@gmail.com>; Lee Hachadoorian <lee.hachadoorian@gmail.com>
Could you not create a function to do this instead?
Set var_limit = 20% of row count
Replace subquery with var_limit
Kris
On 18-Nov-09, at 14:27, Guillaume Lelarge <guillaume@lelarge.info>
wrote:
> Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit :
>> No, It doesn't.
>> In my machine:
>>
>> First select
>> ERROR: syntax error at end of input
>> LINE 1: select * from rapadura.cliente limit 20%
>> ^
>> Second one:
>> ERROR: argument of LIMIT must not contain subqueries
>>
>> Postgres 8.3
>>
>
> It works for Lee because obviously he's working on a 8.4 server. You
> can use
> subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For
> earlier
> releases, there's no way to do this in a single query.
>
>
> --
> Guillaume.
> http://www.postgresqlfr.org
> http://dalibo.com
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT BASED ON PERCENT
2009-11-18 19:12 LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:21 ` Re: LIMIT BASED ON PERCENT Lee Hachadoorian <lee.hachadoorian@gmail.com>
2009-11-18 19:24 ` Re: LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:27 ` Re: LIMIT BASED ON PERCENT Guillaume Lelarge <guillaume@lelarge.info>
2009-11-19 00:20 ` Re: LIMIT BASED ON PERCENT Kris Kewley <kris.kewley@gmail.com>
@ 2009-11-19 06:47 ` Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 11+ messages in thread
From: Guillaume Lelarge @ 2009-11-19 06:47 UTC (permalink / raw)
To: pgsql-sql; +Cc: Kris Kewley <kris.kewley@gmail.com>; Another Trad <anothertrad@gmail.com>; Lee Hachadoorian <lee.hachadoorian@gmail.com>
Le jeudi 19 novembre 2009 à 01:20:24, Kris Kewley a écrit :
> Could you not create a function to do this instead?
>
> Set var_limit = 20% of row count
> Replace subquery with var_limit
>
Sure, see the previous mails from Pavel. You can also put the percent as a
parameter of the function.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2009-11-19 06:47 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-11-18 19:12 LIMIT BASED ON PERCENT Another Trad <anothertrad@gmail.com>
2009-11-18 19:21 ` Lee Hachadoorian <lee.hachadoorian@gmail.com>
2009-11-18 19:24 ` Another Trad <anothertrad@gmail.com>
2009-11-18 19:27 ` Guillaume Lelarge <guillaume@lelarge.info>
2009-11-18 19:28 ` Another Trad <anothertrad@gmail.com>
2009-11-18 19:35 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-11-18 19:30 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-11-18 19:33 ` Lee Hachadoorian <lee.hachadoorian@gmail.com>
2009-11-18 19:39 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-11-19 00:20 ` Kris Kewley <kris.kewley@gmail.com>
2009-11-19 06:47 ` Guillaume Lelarge <guillaume@lelarge.info>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox