get the second highest salary in sql code example

Example 1: sql select second max

Both options you find max as a subset and then exclude from main select
sql> SELECT MAX( col ) FROM table
 	WHERE col < ( SELECT MAX( col ) FROM table);
sql> SELECT MAX(col) FROM table 
WHERE col NOT IN (SELECT MAX(col) FROM table);

Example 2: second max salary in sql

SELECT MAX(SALARY) 'SECOND_MAX' FROM EMPLOYEES
WHERE SALARY <> (SELECT MAX(SALARY) FROM EMPLOYEES);

Example 3: how to get second highest salary in each department in sql

SELECT E.Employers_name, E.dep_number, E.salary
FROM Employers E
WHERE 1 = (SELECT COUNT(DISTINCT salary) 
        FROM Employers B 
        WHERE B.salary > E.salary AND E.dep_number = B.dep_number)
group by E.dep_number

Example 4: how to get employee having maximum experience in mysql

select max(salary), dept_id from employee where salary not in(select max(salary) from employee) group by dept_id;

Tags:

Sql Example

Related