Thread: LIMIT BASED ON PERCENT
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:<br />select * from client limit 20%<br />I have tried (of course, with no success) this:<br />select * from clientlimit ((select count(*) from client)*20/100)<br />
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
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
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
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.orghttp://dalibo.com
But there is any way to do it?<br /><br /><div class="gmail_quote">2009/11/18 Guillaume Lelarge <span dir="ltr"><<a href="mailto:guillaume@lelarge.info">guillaume@lelarge.info</a>></span><br/><blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Le mercredi 18 novembre2009 à 20:24:09, Another Trad a écrit :<br /><div class="im">> No, It doesn't.<br /> > In my machine:<br />><br /> > First select<br /> > ERROR: syntax error at end of input<br /> > LINE 1: select * from rapadura.clientelimit 20%<br /> > ^<br /> > Second one:<br /> >ERROR: argument of LIMIT must not contain subqueries<br /> ><br /> > Postgres 8.3<br /> ><br /><br /></div>Itworks for Lee because obviously he's working on a 8.4 server. You can use<br /> subquery in a LIMIT clause if youhave an 8.4 PostgreSQL server. For earlier<br /> releases, there's no way to do this in a single query.<br /><font color="#888888"><br/><br /> --<br /> Guillaume.<br /> <a href="http://www.postgresqlfr.org" target="_blank">http://www.postgresqlfr.org</a><br/> <a href="http://dalibo.com" target="_blank">http://dalibo.com</a><br/></font></blockquote></div><br />
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 >
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.
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 > >
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
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
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.orghttp://dalibo.com