1.

Given a table in a database having two attributes employee name and their salary in it, find and print the employee having the Nth highest salary amongst them using Structured Query Language (SQL).

Answer»

Example: Consider the following table:

enamesal
P100
Q200
R300
S500
T400
U100

Here, if n = 2, then the output should be 

enamesal
T400

Approach 1: We use the concept of dense_rank() to compute the Nth highest salary. The function DENSE_RANK() returns the rank of a row in an ordered collection of rows as a NUMBER. The ranks are in ascending order, starting with 1. This function takes any numeric data type as an ARGUMENT and returns NUMBER. Based on the VALUES of the value_exprs in the order_by_clause, DENSE RANK computes the rank of each row returned from a query in RELATION to the other rows as an analytic function.

SQL Query:

select * from(select ename, sal, dense_rank() over(order by sal desc)rnk from Employee) where rnk=&n;

Approach 2: In this approach, we first find the employees who have the highest N different wages. The Nth highest salary can then be determined by finding the LOWEST salary among the salaries returned by the above query.

SQL Query:

SELECT * FROM Employee WHERE sal = ( SELECT MIN(sal) FROM Employee WHERE sal IN ( SELECT DISTINCT TOP N sal FROM Employee ORDER BY sal DESC ) );

Approach 3: In this approach, we will create two aliases of the Employee table and then compare each salary with the other salaries present in the table to check if the current salary under consideration is greater than N-1 other salaries.

SQL Query:

SELECT ename,sal from Employee e1 where N-1 = (SELECT COUNT(DISTINCT sal)from Employee e2 where e2.sal > e1.sal)


Discussion

No Comment Found

Related InterviewSolutions