**Requirement:**How to retrieve the n

^{th}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 n^{th}highest**emp_salary AND**n^{th}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_salary

**FROM**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_salary

**FROM**emp

**WHERE**emp_salary

**>(**

**SELECT MIN**

**(**emp_salary

**)**

**FROM**emp

**);**

**Disadvantages of Solution 1:**

- In case we want to get the n
^{th}highest or n^{th}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_salary

**FROM**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 n

^{th}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

**)+**1

**FROM**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 2

^{nd}highest EMP_SALARY replace n with 2, if we need 3

^{rd}highest EMP_SALARY replace n with 3,……, if we need 10

^{th}highest EMPL_SALARY replace n with 10 and soon on.

**SELECT**

x

**.**emp_salary**FROM**

emp x

**WHERE**

**((**

**SELECT COUNT**

**(**

**DISTINCT**y

**.**emp_salary

**)+**1

**FROM**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 2

^{nd}highest salary two times as we have two records with the 2^{nd}highest EMP_SALARY value. In case we need only the 2^{nd}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

**)+**1

**FROM**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 4

^{th}highest EMP_SALARY, as shown below.**SELECT**

**DISTINCT**x

**.**emp_salary

**FROM**

emp x

**WHERE**

**((**

**SELECT COUNT**

**(**

**DISTINCT**y

**.**emp_salary

**)+**1

**FROM**emp y

**)-**4

**)=**

**(**

**SELECT COUNT**

**(**

**DISTINCT**y

**.**emp_salary

**)**

**FROM**emp y

**WHERE**x

**.**emp_salary

**>=**y

**.**emp_salary

**)**

**;**

To get the n

^{th}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 2

^{nd}lowest EMP_SALARY replace n with 2, if we need 3

^{rd}lowest EMP_SALARY replace n with 3,……, if we need 10

^{th}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 2

^{nd}lowest salary two times as we have two records with the 2^{nd}lowest EMP_SALARY value. In case we need only the 2^{nd}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 n

^{th}lowest EMP_SALARY, just by replacing 2 of the above query with the n^{th }value.**Solution 3:**To get the n

^{th}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 2

^{nd}highest EMP_SALARY replace n with 2, if we need 3

^{rd}highest EMP_SALARY replace n with 3,……, if we need 10

^{th}highest EMPL_SALARY replace n with 10 and soon on.

To get the 2

^{nd}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 2

^{nd}highest salary two times as we have two records with the 2^{nd}highest EMP_SALARY value. In case we need only the 2^{nd}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 n

^{th}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.

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.

## No comments :

## Post a Comment