Thread: Re: 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/
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: > 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/ > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >