GETTING TOP5 SALARY EXCLUDING MAX SALARY

Royce Roy

Total Post:149

Points:1043
Posted by  Royce Roy
MYSQL 
 872  View(s)
Ratings:
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.

  1. Takeshi Okada

    Post:89

    Points:629
    Re: Getting top5 salary excluding max salary

    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

  1. jacob rasel

    Post:88

    Points:616
    Re: Getting top5 salary excluding max salary

    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

  1. Mayank Tripathi

    Post:397

    Points:3117
    Re: Getting top5 salary excluding max salary

    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!