Finding the Nth heighest Salary in SQL

Program: 

Write a SQL query to get the Nth highest salary from Employee table. Here N can be any number.

Solution:

SELECT e.* FROM Employee e;

Step1: If you want to find the height salary, initially you need to sort the employee data in descending order or if you want to find the lowest salary you need to sort the employee data in ascending.


Here i am finding the height salary so i will sort the employee data in descending order.

SELECT e.* FROM Employee e ORDER By e.salary DESC;

Step2: After Sorting the data, now give the rownum to the table. 

SELECT ROWNUM rn, Emp.* FROM (SELECT e.* FROM Employee e ORDER By e.salary DESC) Emp;

Step3: Finally, which highest salary do you want to find give that value in where condition.

Here i am finding the 3rd heighest employee information.

SELECT * FROM(SELECT ROWNUM rn, Emp.* FROM (SELECT e.* FROM Employee e ORDER By e.salary DESC) Emp) WHERE rn=3;

Note: rownum works some of the databases only example oracle



MySQL:

mysql>CREATE TABLE Employee(id INT NOT NULL AUTO_INCREMENT,
   firstname VARCHAR(50) NOT NULL,
   lastname VARCHAR(50),
   salary INT,
   designation varchar(5),
   PRIMARY KEY (id));

Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO Employee(firstname, lastname, salary, designation) values('ranga','reddy',50000,'Software Engineer');
Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> INSERT INTO Employee(firstname, lastname, salary, designation) values('vinod','reddy',170000,'Software Developer');
Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> INSERT INTO Employee(firstname, lastname, salary, designation) values('raja','reddy',7000,'Trainee Test Engineer');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> INSERT INTO Employee(firstname, lastname, salary, designation) values('vasu','reddy',79000,'Test Engineer');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> INSERT INTO Employee(firstname, lastname, salary, designation) values('manu','reddy',90,'Engineer');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> select * from Employee;
+----+-----------+----------+--------+-------------+
| id | firstname | lastname | salary | designation |
+----+-----------+----------+--------+-------------+
|  1 | ranga     | reddy    |  50000 | Softw       |
|  2 | vinod     | reddy    | 170000 | Softw       |
|  3 | raja      | reddy    |   7000 | Train       |
|  4 | vasu      | reddy    |  79000 | Test        |
|  5 | manu      | reddy    |     90 | Engin       |
+----+-----------+----------+--------+-------------+
5 rows in set (0.00 sec)

Sort the Employee data in Ascending because i am finding the Nth lowest salary.

mysql> select * from Employee e order by e.salary;
+----+-----------+----------+--------+-------------+
| id | firstname | lastname | salary | designation |
+----+-----------+----------+--------+-------------+
|  5 | manu      | reddy    |     90 | Engin       |
|  3 | raja      | reddy    |   7000 | Train       |
|  1 | ranga     | reddy    |  50000 | Softw       |
|  4 | vasu      | reddy    |  79000 | Test        |
|  2 | vinod     | reddy    | 170000 | Softw       |
+----+-----------+----------+--------+-------------+
5 rows in set (0.00 sec)

In mysql there is keyword called limit is used to find out the limit number of records.

mysql> select * from Employee e order by e.salary limit 2;
+----+-----------+----------+--------+-------------+
| id | firstname | lastname | salary | designation |
+----+-----------+----------+--------+-------------+
|  5 | manu      | reddy    |     90 | Engin       |
|  3 | raja      | reddy    |   7000 | Train       |
+----+-----------+----------+--------+-------------+
2 rows in set (0.00 sec)

In the above example i passed limit value as 2 so it displayed only 2 records. Here Limit is keyword is taking only one argument.

There is one more limit it will take two arguments one is offset and second is maximum number of rows return.

Syntax looks like Limit N-1, No.of records

For example i want to find the 1st lowest employee information then i need to pass like 1-1, 1 ==> 0,1

mysql> select * from Employee e order by e.salary limit 0,1;
+----+-----------+----------+--------+-------------+
| id | firstname | lastname | salary | designation |
+----+-----------+----------+--------+-------------+
|  5 | manu      | reddy    |     90 | Engin       |
+----+-----------+----------+--------+-------------+
1 row in set (0.00 sec)


In the next example we will see finding the top n records.