Write a query in SQL to find the details of an employee having the nth

1.	Write a query in SQL to find the details of an employee having the nth highest salary from a given table Employee. The value of n needs to be taken as user input. Assume that the table Employee has the following schema.
Answer» name - denoting the name of the employee SALARY - denoting the salary of the employee Approach: We USE the dense_rank() function to display the details of the employee having 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. 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. Query: select name, salary from(select name, salary, dense_rank() over(order by salary desc)input from Employee) where input = &n; Explanation: In the above query, we first sort the data according to the descending order of the salary and assign a rank to each of the EMPLOYEES starting from 1. In case of an EVENT where the salary of two employees is the same, they both are assigned the same rank. Then we display the data whose rank is equal to the given input.

Write a query in SQL to find the details of an employee having the nth highest salary from a given table Employee. The value of n needs to be taken as user input. Assume that the table Employee has the following schema.

Answer»

name - denoting the name of the employee
SALARY - denoting the salary of the employee

Approach:

We USE the dense_rank() function to display the details of the employee having 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. 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.

Query:

select name, salary from(select name, salary, dense_rank() over(order by salary desc)input from Employee) where input = &n;

Explanation:

In the above query, we first sort the data according to the descending order of the salary and assign a rank to each of the EMPLOYEES starting from 1. In case of an EVENT where the salary of two employees is the same, they both are assigned the same rank. Then we display the data whose rank is equal to the given input.

Write a query in SQL to find the details of an employee having the nth highest salary from a given table Employee. The value of n needs to be taken as user input. Assume that the table Employee has the following schema.

Discussion

No Comment Found

Related InterviewSolutions

Reply to Comment