Write a query to show the highest and second highest salary of each department from the Employees table..

Answer 1

Attempts?

Answer 2

A͢J SELECT Department ,
MAX(salary) AS 'Highest and Second Maximum Salary'
FROM employees
GROUP BY Department

UNION ALL

SELECT Department ,
MAX (salary) FROM Employees WHERE salary < (SELECT MAX(SALARY) FROM Employees )
Group By Department
ORDER BY Department

Answer 3

SELECT departmentid,
NAME,
salary
FROM
(
SELECT
departmentid,
NAME,
salary,
Dense_rank()OVER (partition BY departmentid
ORDER BY salary DESC) AS Rank,
Count(1)OVER(partition BY departmentid) AS cnt
FROM
employees
)t
WHERE
t.rank = 2
OR ( t.rank = 1
AND cnt = 1 )

Sometimes SQL queries get very complicated by joins, Group By clauses, and other referential dependencies, So those Types of queries can be simplified to proxy data or virtual data which simplifies the queries.
Refer to this article to get more information: https://www.scaler.com/topics/views-in-dbms/