Print   Add To Favorites     Email To Friend   Rate This Article CodeSnip: Selecting nth Highest Value of a Column Using Oracle
 page 3 of 4
by
Feedback
Views (Total / Last 10 Days): 21620/ 21
Article Contents:

Working

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

 « (Page 2) View Entire Article (Page 4) »