Thread: 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 >