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 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

Select max entries in a range in SQL server

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:

​x
 
25000170001700015000140009000​

Rows I want to select

17000
17000
17000

 
170001700017000​

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:

 
24000170001700017000170001700015000140009000​

rows I want to select:

 
1700017000170001700017000​

my sql statement:

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

 
select max(salary)from tablewhere salary between 10000 and 20000order by salary desc;​

This statements only returns 1 row.

Answer

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;

 
SELECT TOP 1 WITH TIES salaryFROM yourTableWHERE salary BETWEEN 10000 AND 20000ORDER BY salary DESC;​

Advertisement

Answer