Search code examples
sqloracle-databaserownum

Accessing second row in result

I have written below query

SELECT DEPT_ID, COUNT(*) AS stud_count FROM TBL_STUDENT_DEPARTMENT_593932       
        GROUP BY DEPT_ID ORDER BY stud_count DESC

It outputs:

DEPT_ID     STUD_COUNT
-------     ----------
Dep02                5
Dep03                4
Dep01                3

I want to select second largest stud_count, i.e. 4. So I used rownum as follows

SELECT stud_count FROM
(
    SELECT DEPT_ID, COUNT(*) AS stud_count FROM TBL_STUDENT_DEPARTMENT_593932       
        GROUP BY DEPT_ID ORDER BY stud_count DESC
)
WHERE ROWNUM = 2;

However it does not return any row, just empty table. How can I do this?

Solution

  • Please check:

    SELECT * FROM
(
    SELECT DEPT_ID, COUNT(*) AS stud_count, ROW_NUMBER() over (order by COUNT(*) desc) ROW_NUM
    FROM TBL_STUDENT_DEPARTMENT_593932       
        GROUP BY DEPT_ID
)
WHERE ROW_NUM = 2;