Select max entries in a range in SQL server

Issue

I have a salary column where I have to select between range of 10000 and 20000 and also the top rows of max salary.

Column I have:

25000
17000
17000
15000
14000
9000

Rows I want to select

17000
17000
17000

I can use top n rows but that will apply on to this column. What I want to do is, say if this column has 2 rows that has max salary, it would select 2, but if another column has 5 rows that has max salary like below, then it should select 5.
column I have:

24000
17000
17000
17000
17000
17000
15000
14000
9000

rows I want to select:

17000
17000
17000
17000
17000

my sql statement:

select max(salary)
from table
where salary between 10000 and 20000
order by salary desc;

This statements only returns 1 row.

Solution

You may use TOP 1 WITH TIES here:

SELECT TOP 1 WITH TIES salary
FROM yourTable
WHERE salary BETWEEN 10000 AND 20000
ORDER BY salary DESC;

Answered By – Tim Biegeleisen

Answer Checked By – Willingham (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.