Обсуждение: Obtaining a limited number of records from a long query
Dear List,
Is there any way to force a query to return just a few records?
For ex, returning just the first ten rows from a long ORDER BY query ?
And then, eventually, obtaining the remaining records at a later time, or in background?
Thanks in advance for your help,
Best,
Oliveiros
Hi Oliveiros Certainly! What you're looking for is the LIMIT...OFFSET syntax. Some examples: SELECT ... LIMIT 10 - return the first 10 records only. SELECT ... LIMIT 10 OFFSET 10 - return 10 records, starting with record 11. Manual page: http://www.postgresql.org/docs/8.3/interactive/queries-limit.html Regards, Andy Oliveiros Cristina wrote: > Dear List, > > Is there any way to force a query to return just a few records? > > For ex, returning just the first ten rows from a long ORDER BY query ? > > And then, eventually, obtaining the remaining records at a later time, > or in background? > > Thanks in advance for your help, > > Best, > Oliveiros
Thanks a million, Andy. It's precisely what I needed, Indeed! Best, Oliveiros ----- Original Message ----- From: "Andy Shellam" <andy-lists@networkmail.eu> To: "Oliveiros Cristina" <oliveiros.cristina@marktest.pt> Cc: <pgsql-sql@postgresql.org> Sent: Monday, May 25, 2009 2:45 PM Subject: Re: [SQL] Obtaining a limited number of records from a long query > Hi Oliveiros > > Certainly! What you're looking for is the LIMIT...OFFSET syntax. > > Some examples: > > SELECT ... LIMIT 10 - return the first 10 records only. > SELECT ... LIMIT 10 OFFSET 10 - return 10 records, starting with record > 11. > > Manual page: > > http://www.postgresql.org/docs/8.3/interactive/queries-limit.html > > Regards, > Andy > > Oliveiros Cristina wrote: >> Dear List, >> Is there any way to force a query to return just a few records? >> For ex, returning just the first ten rows from a long ORDER BY query ? >> And then, eventually, obtaining the remaining records at a later time, >> or in background? >> Thanks in advance for your help, >> Best, >> Oliveiros > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
On Mon, 25 May 2009 14:55:54 +0100 "Oliveiros Cristina" <oliveiros.cristina@marktest.pt> wrote: > > > Thanks a million, Andy. > > It's precisely what I needed, Indeed! Beware: unless you specify an ORDER BY (and you expect no other record is inserted between one query and the other... or you just don't care) you may incur in unexpected results. A query without an order by is not granted to return the result in the same order. If you can (same session) you may use cursors. -- Ivan Sergio Borgonovo http://www.webthatworks.it
Thank you for pointing it out, Ivan. The query I am trying to restrict output for happens to be an ORDER BY query. Actually I wasn't aware of this detail. I'll leave a mental note for myself that results might be unexpected for not ORDER BY queries Best, Oliveiros ----- Original Message ----- From: "Ivan Sergio Borgonovo" <mail@webthatworks.it> To: <pgsql-sql@postgresql.org> Sent: Monday, May 25, 2009 3:13 PM Subject: Re: [SQL] Obtaining a limited number of records from a long query > On Mon, 25 May 2009 14:55:54 +0100 > "Oliveiros Cristina" <oliveiros.cristina@marktest.pt> wrote: > >> >> >> Thanks a million, Andy. >> >> It's precisely what I needed, Indeed! > > Beware: unless you specify an ORDER BY (and you expect no other > record is inserted between one query and the other... or you just > don't care) you may incur in unexpected results. > > A query without an order by is not granted to return the result in > the same order. > > If you can (same session) you may use cursors. > > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
On Mon, 25 May 2009 15:20:57 +0100 "Oliveiros Cristina" <oliveiros.cristina@marktest.pt> wrote: > Thank you for pointing it out, Ivan. > The query I am trying to restrict output for happens to be an > ORDER BY query. > Actually I wasn't aware of this detail. > I'll leave a mental note for myself that results might be > unexpected for not ORDER BY queries a bit more about "unexpected": - if people insert other rows... and by chance they happen to fall before the ones you already retrieved... no matter ofthe ORDER BY, you may "miss" them in the "paginated result". - without an order by there is no "granted" order. This is not a deficiency of PostgreSQL, it is not mandated by sql standardand it offers chances for optimisation The fact that without an order by clause they may return in the same order... is implementation dependent and Postgresql don't behave that way or it happens just by chance. -- Ivan Sergio Borgonovo http://www.webthatworks.it