In this section we will write SQL query to get second highest salary of
Employee. Before writing query its good to be familiar with schema as well as
data in table. Here is the Employee table we will be using this SQL example:
Using Mysql
Using Sql -Server
Using Mysql
mysql>
SELECT *
FROM Employee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
| 1 | James | 10 | 2000 |
| 2 | Jack | 10 | 4000 |
| 3 | Henry | 11 | 6000 |
| 4 | Tom | 11 | 8000 |
+--------+----------+---------+--------+
4 rows IN SET (0.00 sec)
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
| 1 | James | 10 | 2000 |
| 2 | Jack | 10 | 4000 |
| 3 | Henry | 11 | 6000 |
| 4 | Tom | 11 | 8000 |
+--------+----------+---------+--------+
4 rows IN SET (0.00 sec)
If you look data, you will find that second maximum salary in this case
is 6000 and employee name is Henry. Now let’s see some SQL example to find out
this second maximum salary.
Sub queries in SQL are great tool for this kind of scenario, here we first
select maximum salary and then another maximum excluding result of subquery. To
learn more about Subquery see correlate and non-correlate subquery in
SQL
mysql>
SELECT max(salary) FROM
Employee WHERE salary NOT IN
(SELECT
max(salary)
FROM Employee);
+-------------+
| max(salary) |
+-------------+
| 6000 |
+-------------+
1 row IN SET (0.00 sec)
+-------------+
| max(salary) |
+-------------+
| 6000 |
+-------------+
1 row IN SET (0.00 sec)
Here is another SQL query to find second highest salary using subquery
and < operator instead of IN clause:
mysql>
SELECT max(salary) FROM
Employee WHERE salary < (SELECT max(salary) FROM
Employee);
+-------------+
| max(salary) |
+-------------+
| 6000 |
+-------------+
1 row IN SET (0.00 sec)
+-------------+
| max(salary) |
+-------------+
| 6000 |
+-------------+
1 row IN SET (0.00 sec)
TOP keyword of Sybase and SQL Server database is used to select top
record or row of any result set, by carefully using TOP keyword you can find out
second maximum or Nth maximum salary as shown below.
SELECT TOP 1
salary FROM (
SELECT TOP 2
salary FROM employees ORDER BY
salary DESC)
AS emp ORDER
BY salary ASC
Here is what this SQL query is doing : First find out top 2 salary from Employee table and list them in descending order, Now
second highest salary of employee is at top so just take that value. Though you
need to keep in mind of using distinct keyword if there are more
than one employee with top salary, because in that case same salary will be
repeated and TOP 2 may list same salary twice.
Second maximum salary using LIMIT keyword of MYSQL database
LIMIT keyword of MySQL database is little bit similar with TOP keyword of
SQL Server database and allows to take only certain rows from result set. If
you look at below SQL example, its very much similar to SQL Server TOP keyword
example.
mysql>
SELECT salary FROM (SELECT salary FROM
Employee ORDER BY salary DESC
LIMIT 2) AS emp
ORDER BY
salary LIMIT 1;
+--------+
| salary |
+--------+
| 6000 |
+--------+
1 row IN SET (0.00 sec)
+--------+
| salary |
+--------+
| 6000 |
+--------+
1 row IN SET (0.00 sec)
No comments:
Post a Comment