Wednesday, 16 May 2018

Retrieve the nth highest or nth lowest column value from a table

Requirement: How to retrieve the nth highest or nth lowest emp_salary from the EMP table?
Let us take the question in a better way. Let us suppose we have an EMP table with the data as shown below. Now we want to retrieve the nth highest emp_salary AND nth lowest emp_salary from the below table.
Environment Set Up: For the solution of the above requirement let us create an EMP table using the below script.

CREATE TABLE emp (
    emp_id       NUMBER(10)PRIMARYKEY,
    emp_name     VARCHAR2(20),
    emp_salary   NUMBER(10,2)
);

Insert data into EMP table using the below INSERT statements.

INSERT INTO emp VALUES(1,'Susanto',15000.25);
INSERT INTO emp VALUES(2,'Moumita',16000.67);
INSERT INTO emp VALUES(3,'Shuhansh',22000.23);
INSERT INTO emp VALUES(4,'Anjali',12000.65);
INSERT INTO emp VALUES(5,'Ankur',14000.95);
INSERT INTO emp VALUES(6,'Deep',15000.11);

Commit the changes using COMMIT statement.

Thus the data in the EMP table in descending order is shown below:

SELECT*FROM emp ORDER BY emp_salary DESC;


Please note, we don’t have any duplicate EMP_SALARY values. Going forward we will insert some duplicate EMP_SALARY values and try to validate the queries we are going to discuss now.

Solution 1: To get the second highest EMP_SALARY from the EMP table we can use the below statement.

SELECT MAX(emp_salary)second_highest_salaryFROM emp WHERE emp_salary <(SELECT MAX(emp_salary)FROM emp);


Similarly to get the second lowest EMP_SALARY from the EMP table we can use the below statement.

SELECT MIN(emp_salary)second_lowest_salaryFROM emp WHERE emp_salary >(SELECT MIN(emp_salary)FROM emp);


Disadvantages of Solution 1:
  • In case we want to get the nth highest or nth lowest EMP_SALARY from the EMP table, then the queries of Solution 1 may not suffice.
  • It is expensive
  • Also, in case where we have multiple duplicate entries in the EMP_SALARY column (say the second highest value has duplicate entry), and we want to display as many times as the duplicate value occurring in the EMP table in second highest or second lowest position, in this case also, the queries of Solution 1 will display the second highest or second lowest EMP_SALARY, but only time, not as many time as it occurs in the EMP table.
For example, let us enter two more records with duplicate values of the second highest and second lowest EMP_SALARY.

INSERT INTO emp VALUES(7,'Deb',16000.67);
INSERT INTO emp VALUES(8,'Dipendu',14000.95);

Commit the changes using COMMIT statement.

Thus the data in the EMP table in descending order is shown below:


To get the second highest EMP_SALARY as many times as it occurs in the EMP table we can use the below statement.

SELECT MAX(emp_salary) second_highest_salary FROM emp WHERE emp_salary <(SELECT MAX(emp_salary)FROM emp);


We can see clearly that though EMP_SALARY of 16000.67 is present two times, it is displaying only once.

Similarly to get the second lowest EMP_SALARY as many times as it occurs in the EMP table we can use the below statement.

SELECT MIN(emp_salary)second_lowest_salaryFROM emp WHERE emp_salary >(SELECT MIN(emp_salary)FROM emp);


We can see clearly that though EMP_SALARY of 14000.95 is present two times, it is displaying only once.

Hence, in case we want to display the second highest or second lowest EMP_SALARY as many time as it occurs in the EMP table than the above query is not suitable. Hence we should use some other approach which I will be discussing in the latter part of the explanation.

When to use the Solution 1 approach:

When we compulsorily know that we want to display the second highest or the second lowest EMP_SALARY, and we are least bother how many time the second highest or the second lowest EMP_SALARY is present in the EMP table. We are just interested in knowing the second highest or the second lowest EMP_SALARY values irrespective of the number of time it is present in the EMP table.

Solution 2: To get the nth highest EMP_SALARY from the EMP table we can use the below statement

SELECT
    x.emp_salary
FROM
    emp x
WHERE
((SELECT COUNT(DISTINCT y.emp_salary)+1FROM emp y )- n )=
(SELECT COUNT(DISTINCT y.emp_salary)FROM emp y WHERE x.emp_salary >= y.emp_salary)
;

Here ‘n’ is the position for which we need the EMP_SALARY. If we need 2nd highest EMP_SALARY replace n with 2, if we need 3rd highest EMP_SALARY replace n with 3,……, if we need 10th highest EMPL_SALARY replace n with 10 and soon on.

SELECT
   x.emp_salary
FROM
    emp x
WHERE
((SELECT COUNT(DISTINCT y.emp_salary)+1FROM emp y )-2)=
(SELECT COUNT(DISTINCT y.emp_salary)FROM emp y WHERE x.emp_salary >= y.emp_salary)
;


Please note the above query show the 2nd highest salary two times as we have two records with the 2nd highest EMP_SALARY value. In case we need only the 2nd highest EMP_SALARY value irrespective of the number of time it is the EMP table we can use DISTINCT (x.emp_salary) as shown below.

SELECT
DISTINCT x.emp_salary
FROM
    emp x
WHERE
((SELECT COUNT(DISTINCT y.emp_salary)+1FROM emp y )-2)=
(SELECT COUNT(DISTINCT y.emp_salary)FROM emp y WHERE x.emp_salary >= y.emp_salary)
;


Thus, similarly, we can get the 4th highest EMP_SALARY, as shown below.

SELECT
DISTINCT x.emp_salary
FROM
    emp x
WHERE
((SELECT COUNT(DISTINCT y.emp_salary)+1FROM emp y )-4)=
(SELECT COUNT(DISTINCT y.emp_salary)FROM emp y WHERE x.emp_salary >= y.emp_salary)
;


To get the nth lowest EMP_SALARY from the EMP table we can use the below statement

SELECT
DISTINCT x.emp_salary
FROM
    emp x
WHERE
    n =
(SELECT COUNT(DISTINCT y.emp_salary)FROM emp y WHERE x.emp_salary >= y.emp_salary)
; 

Here ‘n’ is the position for which we need the EMP_SALARY. If we need 2nd lowest EMP_SALARY replace n with 2, if we need 3rd lowest EMP_SALARY replace n with 3,……, if we need 10th lowest EMPL_SALARY replace n with 10 and soon on.

SELECT
    x.emp_salary
FROM
    emp x
WHERE
2=
(SELECT COUNT(DISTINCT y.emp_salary)FROM emp y WHERE x.emp_salary >= y.emp_salary)
;


Please note the above query show the 2nd lowest salary two times as we have two records with the 2nd lowest EMP_SALARY value. In case we need only the 2nd lowest EMP_SALARY value irrespective of the number of time it is the EMP table we can use DISTINCT (x.emp_salary) as shown below.

SELECT
DISTINCT x.emp_salary
FROM
    emp x
WHERE
2=
(SELECT COUNT(DISTINCT y.emp_salary)FROM emp y WHERE x.emp_salary >= y.emp_salary)
;


Similarly, we can get any nth lowest EMP_SALARY, just by replacing 2 of the above query with the nth value.

Solution 3: To get the nth highest EMP_SALARY from the EMP table we can use the below statement

SELECT
    emp_salary nth_highest_salary
FROM
(SELECT emp_salary,DENSE_RANK() OVER(ORDER BY emp_salary desc)AS nth_position FROM emp)
WHERE
    nth_position =n;

Here ‘n’ is the position for which we need the EMP_SALARY. If we need 2nd highest EMP_SALARY replace n with 2, if we need 3rd highest EMP_SALARY replace n with 3,……, if we need 10th highest EMPL_SALARY replace n with 10 and soon on.

To get the 2nd highest EMP_SALARY, use the below query. Here we are using ORDER BY emp_salary desc.

SELECT
    emp_salary second_highest_salary
FROM
(SELECT emp_salary,DENSE_RANK() OVER(ORDER BY emp_salary desc)AS nth_position FROM emp)
WHERE
    nth_position =2;


Please note the above query show the 2nd highest salary two times as we have two records with the 2nd highest EMP_SALARY value. In case we need only the 2nd highest EMP_SALARY value irrespective of the number of time it is the EMP table we can use DISTINCT (emp_salary) as shown below.

SELECT
DISTINCT emp_salary second_highest_salary
FROM
(SELECT emp_salary,DENSE_RANK() OVER(ORDER BY emp_salary desc)AS nth_position FROM emp)
WHERE
    nth_position =2;


To get the nth lowest EMP_SALARY from the EMP table we can use the below statement. Here we using ORDER BY emp_salary asc

 SELECT
    emp_salary third_lowest_salary
FROM
(SELECT emp_salary,DENSE_RANK() OVER(ORDER BY emp_salary asc)AS nth_position FROM emp)
WHERE
    nth_position =3;


Advantages of Solution 3: Among all the three solutions this will be the faster and will incur least cost compared to other two.

Hence the solution to our requirement.

If you like the post, please comment, share the post and do like me on Facebook

Thanks & Regards,
Susanto Paul.