Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to limit rows in PostgreSQL SELECT

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

like image 564
Dan Mertz Avatar asked Jul 15 '09 20:07

Dan Mertz


People also ask

How do I limit the number of rows in a SELECT statement?

To limit rows in the result set, use ORDER BY with the optional OFFSET and FETCH clauses. First, the query sorts the rows (ORDER BY). You then tell SQL Server which should be the first row in the result set (OFFSET...

How limit offset works in PostgreSQL?

LIMIT and OFFSET are used when you want to retrieve only a few records from your result of query. LIMIT will retrieve only the number of records specified after the LIMIT keyword, unless the query itself returns fewer records than the number specified by LIMIT.

What is offset and limit in PostgreSQL?

If a limit count is given, no more than that many rows will be returned (but possibly fewer, if the query itself yields fewer rows). LIMIT ALL is the same as omitting the LIMIT clause, as is LIMIT with a NULL argument. OFFSET says to skip that many rows before beginning to return rows.

Does PostgreSQL have limit?

PostgreSQL LIMIT is an optional clause of the SELECT statement that constrains the number of rows returned by the query. The statement returns row_count rows generated by the query. If row_count is zero, the query returns an empty set.


2 Answers

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

SELECT * FROM users LIMIT 5; 
like image 167
Sinan Taifour Avatar answered Oct 13 '22 01:10

Sinan Taifour


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

like image 41
mongotop Avatar answered Oct 13 '22 01:10

mongotop