How to use DENSE_RANK In SQL

1. 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.
2. This function accepts arguments as any numeric data type and returns NUMBER.
3. 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.
4. 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.  

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;

1. To find the 2nd highest sal set n = 2
2. To find the 3rd highest sal set n = 3 and so on.

Let’s check to find 3rd highest salary:

Output: