SQL Query to get highest and 2nd highest salary in employee table with names separated by comma

Transact-SQL
1

Hi,

I use below query to get 2nd highest salary in employee table.

SELECT MAX(SALARY) FROM Employee WHERE SALARY < (SELECT MAX(SALARY) FROM Employee);

How do I get highest and 2nd highest salary with employee names separated by comma in single select statement. Output needed as below
Output:

EmpName1 - $5000, EmpName2 - $ 4500

'EmpName1' is employee with highest salary and 'EmpName2' is 2nd highest by salary. Thanks in advance.

2

use row_number() or dense_rank() depending on do you want in case of a tie.

Query

select *
from   (
         select *, rn = row_number() over (order by SALARY desc)
         from  Employee
       ) as e
where  e.rn = 2
3

Hi,

Thanks for same; substituting 'e.rn = 2' with 'e.rn <= 2' is giving me both highest and 2nd highest in 2 rows; can it be done in single row separated by comma.

4

use string_agg()

5

Thanks for same; accepting as solution.

6 
Hi 

Hope this helps 

SELECT 
    STRING_AGG(CONCAT(EmpName, ' - $', SALARY), ', ') AS EmployeeSalaries
FROM 
    (SELECT TOP 2 EmpName, SALARY 
     FROM Employee 
     ORDER BY SALARY DESC) AS TopSalaries;