You need to use ranking function:
RANK (Transact-SQL) - SQL Server | Microsoft Docs[
^], which returns the rank of each row within the partition of a result set.
Imagine, for the result set of:
{5000, 6000, 7000, 7000, 8000}
there's 2 rows (records) with the rank of two.
SELECT *
FROM (
SELECT [Name], Salary, RANK() OVER(ORDER BY Salary DESC) As RankBySalary
FROM Employee
) T
WHERE T.RankBySalary=2