In order to find nth maximum record of EMPLOYEE_ID column,
the query in listing 1 first does a self-join on itself in the sub query.
Listing 1
select EMPLOYEE_ID from emp a where n=
(select count(*) from emp b where a.EMPLOYEE_ID <= b.EMPLOYEE_ID);
Explanation
There each EMPLOYEE_ID is compared with all the EMPLOYEE_IDs
including itself to return the count =n for that EMPLOYEE_ID which is n times
less than or equal to all the EMPLOYEE_IDs present including itself.
Let me explain with the following example.
Say we have a table having two fields like name and ID as
follows.
ID N
---------- -
1 D
2 E
3 F
4 P
Now say we need to find the 2nd max id from the table, which
is 3 in this case.
Our query compares each id with all the other ids including
itself to find which id is twice less than or equal to as compared to others.
And only id 3 is twice less than or equal to as compared to
all the other records (i.e. 3 <=3 and 3<=4 making the count=2).
Validation for Listing 1 through querying emp table
Listing 2
select EMPLOYEE_ID from emp a where 2=(
select count (*) from emp b where a.EMPLOYEE_ID <= b.EMPLOYEE_ID);
Figure 1
Now let us find another solution.
Listing 3
Select EMPLOYEE_ID FROM (SELECT EMPLOYEE_ID ,RANK() OVER
(ORDER BY EMPLOYEE_ID DESC) DEPTRANK FROM emp) WHERE DEPTRANK=n;
Explanation
This fetches the nth highest record through a table.
The sub query does a vital role here in ranking the
EMPLOYEE_ID column in descending manner.
The major functionality is achieved by rank () over ()
function.
It arranges the EMPLOYEE_ID column in descending order and
does a ranking beginning from highest value. Such that highest EMPLOYEE_ID gets
rank 1, the 2nd highest EMPLOYEE_ID gets rank 2 so on.
The following example illustrates the out come of sub query
used.
Listing 4
SELECT EMPLOYEE_ID, RANK () OVER (ORDER BY EMPLOYEE_ID DESC) DEPTRANK FROM emp;
Figure 2
Then it is the responsibility of the outer query to select
the nth highest record.
Validation for Listing 3 through querying emp table
Listing 5
SELECT EMPLOYEE_ID FROM (SELECT EMPLOYEE_ID, RANK () OVER
(ORDER BY EMPLOYEE_ID DESC) DEPTRANK FROM emp ) WHERE DEPTRANK=2;
Figure 3
