T-SQL query to list top n by department

Question

question

andreytk asked Aug 27, '15

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

10 |1200

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

KenJ commented · Aug 28, 2015 at 05:04 AM

Is this all one table? What have you tried so far?

0 ·

Magnus Ahlkvist commented · Aug 28, 2015 at 09:27 PM

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 ·

andreytk commented · Aug 28, 2015 at 10:14 PM

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 ·

andreytk commented · Aug 28, 2015 at 10:15 PM

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 ·

Answer 1 · 2015-08-28T21:31:40Z

Magnus Ahlkvist answered Aug 28, '15

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

10 |1200

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

andreytk commented · Aug 28, 2015 at 10:31 PM

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 ·

question