Home > DeveloperSection > Forums > Getting top5 salary excluding max salary
Royce Roy
Royce Roy

Total Post:134

Points:938
Posted on    March-02-2015 6:37 AM

 MSSQL Server MYSQL 
Ratings:


 3 Reply(s)
 554  View(s)
Rate this:

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.



Takeshi Okada
Takeshi Okada

Total Post:89

Points:629
Supported
Posted on    March-02-2015 6:41 AM

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

jacob rasel
jacob rasel

Total Post:88

Points:616
Posted on    March-02-2015 6:43 AM

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

Mayank Tripathi
Mayank Tripathi

Total Post:397

Points:3117
Supported
Posted on    March-02-2015 6:44 AM

Hi,

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

Don't want to miss updates? Please click the below button!

Follow MindStick