I am working on a project where there is a requirement to get TOP 5 salaries excluding MAX salary. Suppose MAX salary is 67000, then I have to show top five salaries excluding 67000.
You can use below query to get your desired result
select Top 5 Salary from Employee where Salary < (select MAX(salary) from Employee) order by salary desc
You can use ranking to get top 5 salaries excluding the highest salary
SELECT Top 5 Salary from (SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary Desc) AS Rnk FROM Employee) as emp where emp.Rnk > 1
SQL Server has OFFSET through which you can exclude first row, so you can just sort the records by salary in descending order and remove first row, it will give you what you want.
Here is the SQL Query that could help you:
select Salary from Employee ORDER BY salary DESC OFFSET 1 ROWS FETCH NEXT 5 ROWS ONLY