Thread: Question about restricting the number of rows returned from a query.

Hi,

If I am running a query I know could return 1000's of rows;
is there a PG way to say to just return the first, say, 50 rows?

cheers

steve

See the LIMIT and OFFSET options in the SELECT command in the manual.

-philip

On Wed, 20 Feb 2002, Stephen Ingram wrote:

>
>
> Hi,
>
> If I am running a query I know could return 1000's of rows;
> is there a PG way to say to just return the first, say, 50 rows?
>
> cheers
>
> steve
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

At 20:03 20/02/02, Steve wrote:


>Hi,
>
>If I am running a query I know could return 1000's of rows;
>is there a PG way to say to just return the first, say, 50 rows?

Like this:

SELECT * FROM table LIMIT 50;

If you wanted rows 51-100 you'd use

SELECT * FROM table LIMIT 50 OFFSET 50;

and in general LIMIT n OFFSET m will return rows from m+1 to m+n inclusive.

Bear in mind though that for 'the first 50' to be meaningful you'll likely
have to be using an ORDER BY clause.

best,

Mo




Mo Holkar
Digital Mind Games -- log on to take over
mo.holkar@digitalmindgames.com
http://www.digitalmindgames.com

On Wednesday 20 February 2002 04:05 pm, Mo Holkar wrote:
> At 20:03 20/02/02, Steve wrote:
> >Hi,
> >
> >If I am running a query I know could return 1000's of rows;
> >is there a PG way to say to just return the first, say, 50 rows?
>
> Like this:
>
> SELECT * FROM table LIMIT 50;
>
> If you wanted rows 51-100 you'd use
>
> SELECT * FROM table LIMIT 50 OFFSET 50;
>
> and in general LIMIT n OFFSET m will return rows from m+1 to m+n inclusive.
>
> Bear in mind though that for 'the first 50' to be meaningful you'll likely
> have to be using an ORDER BY clause.
>
> best,
>
> Mo
>


Thanks Mo (and everyone).

I was using a distinct in my case and the results are
just what I need! =:o)

steve





>
>
>
> Mo Holkar
> Digital Mind Games -- log on to take over
> mo.holkar@digitalmindgames.com
> http://www.digitalmindgames.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

hi steve,

i believe the syntax would be:
"SELECT columns
FROM myTable
WHERE myConditions = conditions
LIMIT 50";

additionally, if you'd want to select rows 51 to 100 i think the last
line would read:
OFFSET 50 LIMIT 50

take care,
tuna chatterjee


On Wed, 2002-02-20 at 15:03, Stephen Ingram wrote:
>
>
> Hi,
>
> If I am running a query I know could return 1000's of rows;
> is there a PG way to say to just return the first, say, 50 rows?
>
> cheers
>
> steve
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

On Wednesday 20 February 2002 03:50 pm, Philip Hallstrom wrote:
> See the LIMIT and OFFSET options in the SELECT command in the manual.
>

Thanks Philip.  I did *try* and get onto the postgres sites, but it
kept timing out, so I lazily asked here. Sorry.

And now I found this is psql...  nice!

Thanks again,

steve


lazygitdb=# \h select
Command:     SELECT
Description: Retrieves rows from a table or view
Syntax:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ AS output_name ] [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ { UNION | INTERSECT | EXCEPT [ ALL ] } select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
    [ FOR UPDATE [ OF tablename [, ...] ] ]
    [ LIMIT { count | ALL } [ { OFFSET | , } start ]]

where from_item can be:

[ ONLY ] table_name [ * ]
    [ [ AS ] alias [ ( column_alias_list ) ] ]
|
( select )
    [ AS ] alias [ ( column_alias_list ) ]
|
from_item [ NATURAL ] join_type from_item
    [ ON join_condition | USING ( join_column_list ) ]



> -philip
>
> On Wed, 20 Feb 2002, Stephen Ingram wrote:
> > Hi,
> >
> > If I am running a query I know could return 1000's of rows;
> > is there a PG way to say to just return the first, say, 50 rows?
> >
> > cheers
> >
> > steve
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly