Обсуждение: get certain # of recs

How can one select only a certain number of records in Postgres?

In other SQL langs we can use, say, for the first 20 recs:

select  * from tablename where rownum < 21;

WHAT should 'rownum' be to make this select work... OR is there a diff
syntax?

Thx,
--
=============================================
 Mike S. Nowostawsky:
 Email: mikenowo@sympatico.ca, mikenowo@yahoo.ca
 Home Page: http://www3.sympatico.ca/mikenowo/
 Lachine (Montreal), Quebec, Canada

On Thu, 13 Sep 2001, Mike S. Nowostawsky wrote:

> How can one select only a certain number of records in Postgres?
>
> In other SQL langs we can use, say, for the first 20 recs:
>
> select  * from tablename where rownum < 21;

You could use

select * from tablename limit 20

If you need to sort in some way you could use something like

select col1, col2, ... coln from tablename order by col1 limit 20

Regards,
Patrik Kudo

On Thu, 13 Sep 2001, Mike S. Nowostawsky wrote:

> How can one select only a certain number of records in Postgres?
>
> In other SQL langs we can use, say, for the first 20 recs:
>
> select  * from tablename where rownum < 21;
>
> WHAT should 'rownum' be to make this select work... OR is there a diff
> syntax?

select * from tablename LIMIT 20;

What you want is something like this:

SELECT * FROM tablename LIMIT 21;

This query will return 21 records, but there is no
guarantee which 21 tuples it will return.  Most people
generally add a sort to this type of query so that
they get the records that they want.  For example, I
use a query like this all of the time:

SELECT * FROM caseweights ORDER BY dt DESC LIMIT 10;

In this query dt is a timestamp column, and so this
gives me the 10 latest records in reverse order (from
newest to oldest).  PostgreSQL is very clever about
using the indexes on these queries, and so this is my
method of choice for getting the "most recent"
additions to the table.

Hope this is helpful,

Jason

--- "Mike S. Nowostawsky" <mikenowo@sympatico.ca>
wrote:
> How can one select only a certain number of records
> in Postgres?
>
> In other SQL langs we can use, say, for the first 20
> recs:
>
> select  * from tablename where rownum < 21;
>
> WHAT should 'rownum' be to make this select work...
> OR is there a diff
> syntax?
>
> Thx,
> --
> =============================================
>  Mike S. Nowostawsky:
>  Email: mikenowo@sympatico.ca, mikenowo@yahoo.ca
>  Home Page: http://www3.sympatico.ca/mikenowo/
>  Lachine (Montreal), Quebec, Canada
>
>
>
> ---------------------------(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


__________________________________________________
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/

Take a look at the LIMIT part of the SELECT statement.  in your case you'd
do:

select  * from tablename limit 21;

ALthough you'll want to use an ORDER BY otherwise you could get different
results each time..

-philip

On Thu, 13 Sep 2001, Mike S. Nowostawsky wrote:

> How can one select only a certain number of records in Postgres?
>
> In other SQL langs we can use, say, for the first 20 recs:
>
> select  * from tablename where rownum < 21;
>
> WHAT should 'rownum' be to make this select work... OR is there a diff
> syntax?
>
> Thx,
> --
> =============================================
>  Mike S. Nowostawsky:
>  Email: mikenowo@sympatico.ca, mikenowo@yahoo.ca
>  Home Page: http://www3.sympatico.ca/mikenowo/
>  Lachine (Montreal), Quebec, Canada
>
>
>
> ---------------------------(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
>