93

What's the equivalent to SQL Server's TOP or DB2's FETCH FIRST or mySQL's LIMIT in PostgreSQL?

lospejos
  • 1,976
  • 3
  • 19
  • 35
Dan Mertz
  • 957
  • 1
  • 6
  • 4
  • 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 Jul 15 '09 at 20:51

6 Answers6

145

You can use LIMIT just like in MySQL, for example:

SELECT * FROM users LIMIT 5;
Sinan Taifour
  • 10,521
  • 3
  • 33
  • 30
  • 1
    As suggested by mongotop, to avoid unpredictable results, you need to use an ORDER BY clause. See https://www.postgresql.org/docs/current/static/queries-limit.html – Poiana Apuana Jan 11 '17 at 10:17
35

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

mongotop
  • 7,114
  • 14
  • 51
  • 76
  • 3
    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). – user935714 Aug 05 '16 at 17:23
14

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
Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
8

Use the LIMIT clause or FETCH FIRST 10 ROWS

Hosam Aly
  • 41,555
  • 36
  • 141
  • 182
7

Apart from limit you could use Fetch First as well. Your question already had the answer

Select * from users FETCH FIRST 5 ROWS ONLY
Sarath
  • 2,719
  • 1
  • 31
  • 39
0

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;

arviman
  • 5,087
  • 41
  • 48