How to use DENSE_RANK In SQL
- DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1.
- This function accepts arguments as any numeric data type and returns NUMBER.
- As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause.
- In the above query, the rank is returned based on the sal of the employee table. In the case of a tie, it assigns equal rank to all the rows.
How to find Nth highest salary from a table?
Structured Query Language is a computer language that we use to interact with a relational database. Finding Nth highest salary in a table is the most common question asked in interviews. Here is a way to do this task using the dense_rank() function.
Consider the following table:
Employee:
CREATE TABLE:
CREATE TABLE emp (
emp_name VARCHAR(50),
emp_salary DECIMAL(10,2)
);
Let’s insert some random data with a random name and then we will look at how to calculate the nth highest emp_salary.
Query:
CREATE TABLE emp (
emp_name VARCHAR(50),
emp_salary DECIMAL(10,2)
);
INSERT INTO emp (emp_name, emp_salary) VALUES
('Shubham Thakur', 50000.00),
('Aman Chopra', 60000.50),
('Naveen Tulasi', 75000.75),
('Bhavika uppala', 45000.25),
('Nishant jain', 80000.00);
Output:
Query:
SELECT * FROM (
SELECT emp_name, emp_salary, DENSE_RANK() OVER (ORDER BY emp_salary DESC) AS r
FROM emp
) AS subquery
WHERE r = 3;
- To find the 2nd highest sal set n = 2
- To find the 3rd highest sal set n = 3 and so on.
Let’s check to find 3rd highest salary:
Output: