如何在PostgreSQL查询中添加LIMIT子句？

Question

我想在postgresql查询中添加LIMIT子句，但它给我一个错误。

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

以下是错误信息：

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

- sumit kundan

Answer 1

对于上述例子，请跳过“0,”这一部分。

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 不是postgres方言，请使用OFFSET。例如，如果您想要下一个10个结果：

OFFSET 10 LIMIT 10

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

- MrAleister

Answer 2

查询应该是选择前10行

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

如果您想选择第 y 条记录(start count from 0)之后的 x 行，请使用

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

希望这可以帮到你！

- Mohamed Anees A