Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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:

25000
17000
17000
15000
14000
9000

Rows I want to select

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading