CurlyCream CurlyCream - 5 months ago 32
SQL Question

SQL query for second largest value with a where condition

I have a table with step id and resident id. I wanted to write a query find the second largest step id for a particular resident.

The query using max in the google examples doesn't have the where clause.
Tried this:

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


Table:

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


Result am expecting is 856. but i am getting the below error,

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: -

Answer

If the step ids are distinct, you can just use OFFSET and 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;

If they are not distinct, just add a GROUP BY or 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;
Comments