Thread: Obtaining a limited number of records from a long query

Obtaining a limited number of records from a long query

From
"Oliveiros Cristina"
Date:
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

Re: Obtaining a limited number of records from a long query

From
Andy Shellam
Date:
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


Re: Obtaining a limited number of records from a long query

From
"Oliveiros Cristina"
Date:

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
> 



Re: Obtaining a limited number of records from a long query

From
Ivan Sergio Borgonovo
Date:
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



Re: Obtaining a limited number of records from a long query

From
"Oliveiros Cristina"
Date:
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
> 



Re: Obtaining a limited number of records from a long query

From
Ivan Sergio Borgonovo
Date:
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