How To Notice Minute Highest Or Maximum Salary Of Employee Inward Sql - Interview Question
How to notice 2nd highest or 2nd maximum salary of an Employee is i of the most oftentimes asked SQL interview inquiry similar to finding duplicate records inwards table in addition to when to purpose truncate vs delete. There are many ways to notice 2nd highest salary based upon which database you lot are using every bit dissimilar database provides dissimilar characteristic which tin post away hold out used to notice second maximum or Nth maximum salary of employee. Well this inquiry tin post away also hold out generalized alongside other scenario similar finding 2nd maximum historic menses etc. In this SQL tutorial nosotros volition run into dissimilar instance of SELECT SQL query to notice 2nd highest salary independent of databases or you lot may telephone telephone inwards ANSI SQL in addition to other SQL queries which uses database specific characteristic to notice 2nd maximum salary.
SQL query to notice 2nd maximum salary of Employee
In this department nosotros volition write SQL query to acquire 2nd highest salary of Employee. Before writing query its practiced to hold out familiar alongside schema every bit good every bit information inwards table. Here is the Employee tabular array nosotros volition hold out using this SQL example: 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 lot expect data, you lot volition notice that 2nd maximum salary inwards this instance is 6000 in addition to employee mention is Henry. Now let’s run into simply about SQL instance to notice out this 2nd maximum salary.
Second maximum salary using sub query in addition to IN clause
Sub queries inwards SQL are corking tool for this variety of scenario, hither nosotros get-go guide maximum salary in addition to and then simply about other maximum excluding number of subquery. To larn to a greater extent than most Subquery see correlate in addition to non-correlate subquery inwards 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 simply about other SQL query to notice 2nd highest salary using subquery in addition to < 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)
Both of higher upwardly SQL instance volition plough over off all database including Oracle, MySQL, Sybase in addition to SQL Server every bit they are written using criterion SQL keywords. But sometime you lot tin post away also purpose database specific features similar TOP keyword of SQL Server or Sybase database to notice out 2nd highest salary of Employee.
Second highest salary using TOP keyword of Sybase or SQL Server database
TOP keyword of Sybase in addition to SQL Server database is used to guide locomote past times tape or row of whatever number set, past times carefully using TOP keyword you lot tin post away notice out 2nd maximum or Nth maximum salary every bit 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 notice out locomote past times two salary from Employee table in addition to listing them inwards descending order, Now 2nd highest salary of employee is at locomote past times hence simply receive got that value. Though you lot demand to continue inwards heed of using distinct keyword if at that spot are to a greater extent than than i employee alongside locomote past times salary, because inwards that instance same salary volition hold out repeated in addition to TOP two may listing same salary twice.
Second maximum salary using LIMIT keyword of MYSQL database
LIMIT keyword of MySQL database is trivial chip similar alongside TOP keyword of SQL Server database in addition to allows to receive got solely sure as shooting rows from number set. If you lot expect at below SQL example, its real 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)
That’s on How to notice 2nd highest salary of Employee using SQL query. This is practiced inquiry which actually evidence your SQL knowledge, its non tough but definitely tricky for beginners. As follow upwardly inquiry you lot tin post away inquire him to notice tertiary maximum salary or Nth maximum salary every bit well.
Further Learning
Introduction to SQL past times Jon Flemish region
Introduction to SQL Server
Head First SQL
Other SQL Interview Question answers you lot may like
Komentar
Posting Komentar