Find the second highest salary for each department.
Sample Data
Query
SELECT EMPID,EMPName,EMPDept,EMPSal FROM
(
SELECT DENSE_RANK() OVER(partition by EMPDept ORDER BY EMPSAL DESC)AS RowId, *
FROM [dbo].#tmp_EMP
) AS e1
WHERE e1.RowId = 2
(
SELECT DENSE_RANK() OVER(partition by EMPDept ORDER BY EMPSAL DESC)AS RowId, *
FROM [dbo].#tmp_EMP
) AS e1
WHERE e1.RowId = 2
Output
SQLAssist 
very useful info lista de emails lista de emails lista de emails lista de emails lista de emails