get certain # of recs

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?

use the LIMIT clause, e.g.

SELECT * FROM tablename LIMIT 20

for the first 20 rows, or

SELECT * FROM tablename LIMIT 10,20

for 10 rows beginning from the 20th row (or possibly the other way round,
the caffeine isn't working yet ;-)

When using LIMIT it is generally advisable to also use ORDER BY in order to
guarantee consistent result sets over repeated queries.

HTH

Ian Barwick

--
Ian Barwick - Developer
http://www.akademie.de

Remove SUNGLASSES to reply ;-)

You might also want to look at the SQL command
CLUSTER, which physically reorders the table rows
based upon an existing index.

Andrew Gould

--- Ian Barwick <SUNGLASSESbarwick@gmx.net> wrote:

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?

use the LIMIT clause, e.g.

SELECT * FROM tablename LIMIT 20

for the first 20 rows, or

SELECT * FROM tablename LIMIT 10,20

for 10 rows beginning from the 20th row (or possibly
the other way round,
the caffeine isn't working yet ;-)

When using LIMIT it is generally advisable to also
use ORDER BY in order to
guarantee consistent result sets over repeated
queries.

HTH

Ian Barwick

--
Ian Barwick - Developer
http://www.akademie.de

Remove SUNGLASSES to reply ;-)

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

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

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

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:

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/

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;

Note:

In postgresql, CLUSTER is severly broken. Don't use it if you value your
data.

...Maybe its time to yank cluster completely?

-alex
On Fri, 14 Sep 2001, Andrew Gould wrote: