MySql subquery and row_number function

MySql subquery and row_number function

A subquery is a SQL query nested inside another SQL query.

We can add subquery in either SELECT clausem FROM clause or WHERE clause.

MySql subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement.

Simple MySql subquery syntax,

SELECT column1, column2, ..
FROM table-name
WHERE expression operator (SELECT column1, column2 ... FROM table-name)

MySql query to get nth highest salary in employee table

Let us consider the employee table 'tblemployee' rows,

mysql> select * from tblemployee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
|      2 | emp2     |      11 |  20000 |
|      3 | emp3     |      10 |  15000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
4 rows in set (0.00 sec)

Using subquery,

This is the mysql subquery to find the nth highest salary in employee table 'tblemployee'.

We need to use the required nth_highest value to corresponding position.

select * 
from tblemployee emp1 
where (nth_highest-1)=(select count(distinct(emp2.salary)) 
                       from  tblemployee emp2 
                       where emp2.salary > emp1.salary);

Suppose we need to get 3rd highest salary in employee table 'tblemployee'.

mysql> select * from tblemployee emp1 
where (3-1)=(select count(distinct(emp2.salary)) 
from  tblemployee emp2 
where emp2.salary > emp1.salary);

+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
1 row in set (0.00 sec)

Using rownumber function,

select emp.* 
from (select emp1.* , row_number() over(order by emp1.salary desc) 'row_num' 
from tblemployee emp1) emp 
where emp.row_num = nth_highest;

Suppose we need to get 3rd highest salary in employee table 'tblemployee'.

mysql> select emp.* 
from (select emp1.* , row_number() over(order by emp1.salary desc) 'row_num' 
from tblemployee emp1) emp 
where emp.row_num = 3;

+--------+----------+---------+--------+---------+
| emp_id | emp_name | dept_id | salary | row_num |
+--------+----------+---------+--------+---------+
|      4 | emp4     |      11 |  12000 |       3 |
+--------+----------+---------+--------+---------+
1 row in set (0.00 sec)

mysql query to get the employee list order by highest salaries,

mysql> select emp.* 
from (select emp1.* , row_number() over(order by emp1.salary desc) 'row_num' 
from tblemployee emp1) emp;

+--------+----------+---------+--------+---------+
| emp_id | emp_name | dept_id | salary | row_num |
+--------+----------+---------+--------+---------+
|      2 | emp2     |      11 |  20000 |       1 |
|      3 | emp3     |      10 |  15000 |       2 |
|      4 | emp4     |      11 |  12000 |       3 |
|      1 | emp1     |      10 |  10000 |       4 |
+--------+----------+---------+--------+---------+
4 rows in set (0.00 sec)

« Previous Next »