Select max entries in a range in SQL server-CodePudding

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:

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:

rows I want to select:

my sql statement:

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

This statements only returns 1 row.

CodePudding user response：

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;