Thread

1. get certain # of recs

   Mike S. Nowostawsky <mikenowo@sympatico.ca> — 2001-09-14T03:44:29Z

   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
   
   
   
   

2. Re: get certain # of recs

   Ian Barwick <sunglassesbarwick@gmx.net> — 2001-09-14T10:35:40Z

   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 ;-)
   
   

3. Re: get certain # of recs

   Andrew Gould <andrewgould@yahoo.com> — 2001-09-14T14:58:25Z

   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/
   
   

4. Re: get certain # of recs

   Patrik Kudo <kudo@partitur.se> — 2001-09-14T21:09:11Z

   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
   
   
   

5. Re: get certain # of recs

   Philip Hallstrom <philip@adhesivemedia.com> — 2001-09-14T21:35:45Z

   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
   >
   
   
   

6. Re: get certain # of recs

   Jason Earl <jdearl@yahoo.com> — 2001-09-14T21:36:09Z

   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/
   
   

7. Re: get certain # of recs

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-09-14T22:26:08Z

   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;
   
   
   

8. CLUSTER (was Re: get certain # of recs)

   Alex Pilosov <alex@pilosoft.com> — 2001-09-15T02:38:00Z

   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)
   > 
   >