我有一张带有步骤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不同,则可以只使用OFFSET
和FIRST 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 BY
或SELECT 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;