Second or Third Highest Record in SQL

When data is given to us and we want to find out Top 3 or Bottom 5 records on the basis of some attribute, this can be easily solved by "Order By" clause with "Limit"

Suppose the Data is given in this format:

SQL Query to get Top 5 records based on Salary

Query:

SELECT * FROM EMPLOYEE

ORDER BY salary DESC

LIMIT 5;

Things are fine till this point. But suppose you just need Top 3rd Salaried Employee only. Here it is not possible through Order and Limit clause.

We need here a ROW_NUMBER() function to get this thing done. It assigns a number to each of the rows and later you can select which row number you want to select.

Query:

SELECT NAME, 

        SALARY, 

        ROW_NUMBER() OVER(ORDER BY salary DESC) as row_num

FROM Employee;

It allocates rank to each record based on salary. Record having the highest salary will get Rank as 1 and second highest will get Rank as 2.

 

Now our objective was to get the Employee having 3rd highest salary i.e. Merin.

 

In the above query, just filter out the row_num = 3 and we will get our intended result.

Query:

SELECT Name, Salary 

FROM

  (

      SELECT NAME, 

                     SALARY, 

                     ROW_NUMBER() OVER(ORDER BY salary DESC) as row_num

       FROM Employee

  ) temp

WHERE temp.row_num = 3;

Now, next thing would be how to get the highest record category wise.

Suppose we have a data set of country-state wise population and now what we want is that country wise highest populated state.

To solve this problem, we need to partition our data based on country. We will look at the query in next article. Stay tuned!!