pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedObtaining a limited number of records from a long query
8+ messages / 5 participants
[nested] [flat]
* Obtaining a limited number of records from a long query
@ 2009-05-25 13:32 Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-25 13:42 ` Re: Obtaining a limited number of records from a long query Luiz Eduardo Cantanhede Neri <lecneri@gmail.com>
2009-05-25 13:45 ` Re: Obtaining a limited number of records from a long query Andy Shellam <andy-lists@networkmail.eu>
2009-05-25 14:15 ` Re: Obtaining a limited number of records from a long query Daniel Staal <DStaal@usa.net>
0 siblings, 3 replies; 8+ messages in thread
From: Oliveiros Cristina @ 2009-05-25 13:32 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Obtaining a limited number of records from a long query
2009-05-25 13:32 Obtaining a limited number of records from a long query Oliveiros Cristina <oliveiros.cristina@marktest.pt>
@ 2009-05-25 13:42 ` Luiz Eduardo Cantanhede Neri <lecneri@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Luiz Eduardo Cantanhede Neri @ 2009-05-25 13:42 UTC (permalink / raw)
To: Oliveiros Cristina <oliveiros.cristina@marktest.pt>; +Cc: pgsql-sql; pgsql-novice@postgresql.org
Visit http://www.sqlzoo.net/howto/source/z.dir/tip915069/postgres and u'll
get ur awnser and others like from 11th and on.
For now you may try SELECT * FROM cia LIMIT 10
On Mon, May 25, 2009 at 10:32 AM, Oliveiros Cristina <
oliveiros.cristina@marktest.pt> 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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Obtaining a limited number of records from a long query
2009-05-25 13:32 Obtaining a limited number of records from a long query Oliveiros Cristina <oliveiros.cristina@marktest.pt>
@ 2009-05-25 13:45 ` Andy Shellam <andy-lists@networkmail.eu>
2009-05-25 13:55 ` Re: Obtaining a limited number of records from a long query Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2 siblings, 1 reply; 8+ messages in thread
From: Andy Shellam @ 2009-05-25 13:45 UTC (permalink / raw)
To: Oliveiros Cristina <oliveiros.cristina@marktest.pt>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Obtaining a limited number of records from a long query
2009-05-25 13:32 Obtaining a limited number of records from a long query Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-25 13:45 ` Re: Obtaining a limited number of records from a long query Andy Shellam <andy-lists@networkmail.eu>
@ 2009-05-25 13:55 ` Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-25 14:13 ` Re: Obtaining a limited number of records from a long query Ivan Sergio Borgonovo <mail@webthatworks.it>
0 siblings, 1 reply; 8+ messages in thread
From: Oliveiros Cristina @ 2009-05-25 13:55 UTC (permalink / raw)
To: Andy Shellam <andy-lists@networkmail.eu>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Obtaining a limited number of records from a long query
2009-05-25 13:32 Obtaining a limited number of records from a long query Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-25 13:45 ` Re: Obtaining a limited number of records from a long query Andy Shellam <andy-lists@networkmail.eu>
2009-05-25 13:55 ` Re: Obtaining a limited number of records from a long query Oliveiros Cristina <oliveiros.cristina@marktest.pt>
@ 2009-05-25 14:13 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
2009-05-25 14:20 ` Re: Obtaining a limited number of records from a long query Oliveiros Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 8+ messages in thread
From: Ivan Sergio Borgonovo @ 2009-05-25 14:13 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Obtaining a limited number of records from a long query
2009-05-25 13:32 Obtaining a limited number of records from a long query Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-25 13:45 ` Re: Obtaining a limited number of records from a long query Andy Shellam <andy-lists@networkmail.eu>
2009-05-25 13:55 ` Re: Obtaining a limited number of records from a long query Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-25 14:13 ` Re: Obtaining a limited number of records from a long query Ivan Sergio Borgonovo <mail@webthatworks.it>
@ 2009-05-25 14:20 ` Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-25 14:56 ` Re: Obtaining a limited number of records from a long query Ivan Sergio Borgonovo <mail@webthatworks.it>
0 siblings, 1 reply; 8+ messages in thread
From: Oliveiros Cristina @ 2009-05-25 14:20 UTC (permalink / raw)
To: Ivan Sergio Borgonovo <mail@webthatworks.it>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Obtaining a limited number of records from a long query
2009-05-25 13:32 Obtaining a limited number of records from a long query Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-25 13:45 ` Re: Obtaining a limited number of records from a long query Andy Shellam <andy-lists@networkmail.eu>
2009-05-25 13:55 ` Re: Obtaining a limited number of records from a long query Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-25 14:13 ` Re: Obtaining a limited number of records from a long query Ivan Sergio Borgonovo <mail@webthatworks.it>
2009-05-25 14:20 ` Re: Obtaining a limited number of records from a long query Oliveiros Cristina <oliveiros.cristina@marktest.pt>
@ 2009-05-25 14:56 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
0 siblings, 0 replies; 8+ messages in thread
From: Ivan Sergio Borgonovo @ 2009-05-25 14:56 UTC (permalink / raw)
To: 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 of the 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 standard and
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Obtaining a limited number of records from a long query
2009-05-25 13:32 Obtaining a limited number of records from a long query Oliveiros Cristina <oliveiros.cristina@marktest.pt>
@ 2009-05-25 14:15 ` Daniel Staal <DStaal@usa.net>
2 siblings, 0 replies; 8+ messages in thread
From: Daniel Staal @ 2009-05-25 14:15 UTC (permalink / raw)
To: pgsql-novice@postgresql.org
--As of May 25, 2009 2:32:14 PM +0100, Oliveiros Cristina is alleged to
have said:
> 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,
--As for the rest, it is mine.
Depending on what you want, either LIMIT and OFFSET or a cursor will help
you.
<http://www.postgresql.org/docs/8.3/interactive/queries-limit.html;
<http://www.postgresql.org/docs/8.3/interactive/sql-declare.html;
Daniel T. Staal
---------------------------------------------------------------
This email copyright the author. Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes. This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2009-05-25 14:56 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-05-25 13:32 Obtaining a limited number of records from a long query Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-25 13:42 ` Luiz Eduardo Cantanhede Neri <lecneri@gmail.com>
2009-05-25 13:45 ` Andy Shellam <andy-lists@networkmail.eu>
2009-05-25 13:55 ` Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-25 14:13 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
2009-05-25 14:20 ` Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-25 14:56 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
2009-05-25 14:15 ` Daniel Staal <DStaal@usa.net>
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