CODEWITHSUNDEEP
postgresql
sumit kundan
sumit kundan

Reputation: 173

How to add LIMIT clause in postgressql Query?

I want to add LIMIT clause in postgressql query but its give me error

SELECT complaint_id FROM complaint_details_v2 a where a.road_dept SIMILAR TO 'PWDBnR' order by a.server_time desc LIMIT 0, 10

Below is the error:

ERROR: LIMIT #,# syntax is not supported
SQL state: 42601
Hint: Use separate LIMIT and OFFSET clauses.
Character: 87

Upvotes: 4

Views: 4416

Answers (2)

Mohamed Anees A
Mohamed Anees A

Reputation: 4591

Query should be for selecting first 10 rows

SELECT complaint_id FROM complaint_details_v2 a where a.road_dept SIMILAR TO 'PWDBnR' order by a.server_time desc LIMIT 10

If you want to select x rows after y records(start count from 0), You should use

SELECT complaint_id FROM complaint_details_v2 a where a.road_dept SIMILAR TO 'PWDBnR' order by a.server_time desc LIMIT x OFFSET y

Hope this helps!

Upvotes: 0

MrAleister
MrAleister

Reputation: 1581

For the example above - skip the '0,' bit 

SELECT complaint_id FROM complaint_details_v2 a where a.road_dept SIMILAR TO 
'PWDBnR' order by a.server_time desc LIMIT 10

LIMIT 0, 10 is not postgres dialect, use OFFSET. For example if you want to next 10 results: 

SELECT complaint_id FROM complaint_details_v2 a where a.road_dept SIMILAR TO 
'PWDBnR' order by a.server_time desc OFFSET 10 LIMIT 10

http://www.sqlines.com/postgresql/limit_offset

Upvotes: 5

Related Questions