我有一张带有步骤ID和居民ID的表格。我想写一个查询来查找特定居民的第二大步骤ID。

Google示例中使用max的查询没有where子句。
试过这个:

SELECT DISTINCT(step_action_id)
FROM step_table where resident_id =219
ORDER BY step_action_id DESC limit 2,1;


表:

Step_Action_id REsident_id
800               218
801               218
911               218
857               218
856               219
858               219


结果预期为856。但是我收到以下错误,

Error: ILLEGAL USE OF KEYWORD LIMIT.  TOKEN SKIP OPTIMIZE FOR FETCH , EXCEPT MINUS UNION <END-OF-STATEMENT> WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=3.59.81
SQLState:  42601
ErrorCode: -199
Error: THE CURSOR SQL_CURLH200C1 IS NOT IN A PREPARED STATE. SQLCODE=-514, SQLSTATE=26501, DRIVER=3.59.81
SQLState:  26501
ErrorCode: -

最佳答案

如果步骤ID不同,则可以只使用OFFSETFIRST FIRST 1 ROW ONLY

SELECT step_action_id
FROM step_table
WHERE resident_id = 219
ORDER BY step_action_id DESC
OFFSET 1 ROWS
FETCH FIRST 1 ROW ONLY;


如果它们没有区别,则只需添加GROUP BYSELECT DISTINCT

SELECT step_action_id
FROM step_table
WHERE resident_id = 219
GROUP BY step_action_id
ORDER BY step_action_id DESC
OFFSET 1 ROWS
FETCH FIRST 1 ROW ONLY;

10-07 19:04
查看更多