question

andreytk avatar image
andreytk asked

T-SQL query to list top n by department

Tables has the following columns: dept_name, varchar last_name, varchar first_name, varchar salary, money I need to run a query to list top 5 paid employees for each department. Can somebody help, please?
top
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is this all one table? What have you tried so far?
0 Likes 0 ·
When you ask a question on ASK.sqlservercentral.com, please use tags to state which version of SQL Server you are running. That helps us advice you with T-SQL-code which will work for your version of SQL Server.
0 Likes 0 ·
Thank you for your response. Yes, this is one table. This is what I've tried so far: SELECT TOP (5) WITH TIES dept_name, last_name, salary FROM HR.Salary ORDER BY salary DESC;
0 Likes 0 ·
Thank you for your response. Yes, this is one table. This is what I've tried so far: SELECT TOP (5) WITH TIES dept_name, last_name, salary FROM HR.Salary ORDER BY salary DESC;
0 Likes 0 ·

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Something like this would work (untested, there are usually some typos in my code, so it might not work directly) WITH CTE AS( SELECT dept_name, first_name, dept_name, salary, ROW_NUMBER() OVER(PARTITION BY dept_name ORDER BY salary DESC) AS RowNum FROM TheTable )SELECT first_name, last_name, dept_name, salary FROM CTE WHERE RowNumber
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you, it worked with some minor spelling corrections: WITH CTE AS( SELECT dept_name, first_name, dept_name, salary, ROW_NUMBER() OVER(PARTITION BY dept_name ORDER BY salary DESC) AS RowNum FROM TheTable )SELECT first_name, last_name, dept_name, salary FROM CTE WHERE RowNum <= 5 ORDER BY dept_name, RowNum
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.