/SQL/How to get second highest salary in each department in sql

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

Sql find second highest salary employee

/* sql 2nd highest salary employee */
select sal, ename
from emp
where sal =
    (
        select max(sal) from emp where sal <
            (select max(sal) from emp)
    )
----------------------------------------------- option 2
select *
from 
(
    select ename, sal, dense_rank() over(order by sal desc) rank
    from emp
)
where rank =2;

Get max salary from each department sql

--Find out the name of top earner in each departments
--Output has Name, Department name and max salary of the department

SELECT E.FIRST_NAME , D.DEPARTMENT_NAME, E.SALARY
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE SALARY IN(SELECT MAX(E.SALARY)
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME);

Similar codes

0 vote278 views1 answer
Deleted table in sql trigger
SQL
1 vote308 views1 answer
Sample in sql
SQL
-1 vote325 views1 answer
Can pandas fetch data from sql
SQL
3 votes325 views1 answer
Postgres add not null to existing column
SQL
0 vote332 views1 answer
Mysqli last row
SQL
1 vote291 views1 answer
Mysqli connect
SQL
1 vote333 views1 answer
Psql connect
SQL
0 vote307 views1 answer
Sql "List the contact methods found in the Contact table.For each contact method, list how many people chose that contact method."
SQL
57 votes547 views5 answers
How to create a new user in postgresql
SQL
6 votes389 views2 answers
Install psql mac
SQL