CODEWITHSUNDEEP
sql-server
Surya sasidhar
Surya sasidhar

Reputation: 30293

SQL query to find the top rows in SQL Server

In SQL Server, I am trying to get the top 5 salaries.

I have salaries like 

5000
5000
4500
4500
3000
2000 
1000
500
400

and I'd like to get

5000
5000
4500
4500
3000
2000
1000

Upvotes: 1

Views: 251

Answers (5)

Victor Capeluto
Victor Capeluto

Reputation: 106

SELECT TOP 5 Salary
FROM [Table]
GROUP BY Salary
ORDER BY Salary DESC

You need to group the query to avoid the duplicated salaries.

Upvotes: 0

marc_s
marc_s

Reputation: 754230

If you want to get the top 5 distinct salaries (no matter how many times the same amount might show up), you need to use the DENSE_RANK() ranking function and a CTE to achieve this:

DECLARE @salaries TABLE (salary DECIMAL(18,4))

INSERT INTO @salaries VALUES(5000)
INSERT INTO @salaries VALUES(5000)
INSERT INTO @salaries VALUES(4500)
INSERT INTO @salaries VALUES(4500)
INSERT INTO @salaries VALUES(3000)
INSERT INTO @salaries VALUES(2000)
INSERT INTO @salaries VALUES(1000)
INSERT INTO @salaries VALUES(500)
INSERT INTO @salaries VALUES(400)

;WITH SalariesRanked AS
(
    SELECT 
        Salary, 
        SalaryNumber = DENSE_RANK() OVER(ORDER BY Salary DESC)
    FROM    
        @salaries
)
SELECT salary
FROM SalariesRanked 
WHERE SalaryNumber <= 5

This results in an output like this:

salary
5000.0000
5000.0000
4500.0000
4500.0000
3000.0000
2000.0000
1000.0000

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

select salary
from
  (
    select salary,
           dense_rank() over(order by salary desc) as rn
    from YourTable
  ) as T
where rn <= 5

Try on SE-Data

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166326

Have a look at using TOP (Transact-SQL)

Upvotes: 0

Marco
Marco

Reputation: 57573

SELECT TOP 5 salary FROM your_table
ORDER BY salary DESC

Upvotes: 3

Related Questions