What's the equivalent to SQL Server's TOP or DB2's FETCH FIRST or mySQL's LIMIT in PostgreSQL?
-
How funny...I just wanted to know this yesterday! (of course, now I don't need to know any more, but I'll vote you up anyway since I could have used it then.)Beska– Beska2009-07-15 20:51:36 +00:00Commented Jul 15, 2009 at 20:51
Add a comment
|
6 Answers
You can use LIMIT just like in MySQL, for example:
SELECT * FROM users LIMIT 5;
1 Comment
Poiana Apuana
As suggested by mongotop, to avoid unpredictable results, you need to use an ORDER BY clause. See postgresql.org/docs/current/static/queries-limit.html
You could always add the OFFSET clause along with LIMIT clause.
You may need to pick up a set of records from a particular offset. Here is an example which picks up 3 records starting from 3rd position:
testdb=# SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
This would produce the following result:
id | name | age | address | salary
----+-------+-----+-----------+--------
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
Full explanation and more examples check HERE
1 Comment
user935714
When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows (from documentation).
On PostgreSQL, there are two ways to achieve this goal.
SQL Standard
The first option is to use the SQL:2008 standard way of limiting a result set using the FETCH FIRST N ROWS ONLY syntax:
SELECT
title
FROM
post
ORDER BY
id DESC
FETCH FIRST 50 ROWS ONLY
The SQL:2008 standard syntax is supported since PostgreSQL 8.4.
PostgreSQL 8.3 or older
For PostgreSQL 8.3 or older versions, you need the LIMIT clause to restrict the result set size:
SELECT
title
FROM
post
ORDER BY
id DESC
LIMIT 50
Comments
Apart from limit you could use Fetch First as well. Your question already had the answer
Select * from users FETCH FIRST 5 ROWS ONLY
Comments
If you want to select 10 students skipping the top 3, (hence fetching students of rank 4 to 13 inclusive) from a table ordered by a rank column for example, you'd use:
SELECT * FROM STUDENTS ORDER BY rank asc LIMIT 10 OFFSET 3;
If you just want the top 10, you'd omit OFFSET:
SELECT * FROM STUDENTS ORDER BY rank asc LIMIT 10;
