How to get the top N rows per group in SQL

"The highest-paid person in each department" sounds like a GROUP BY. It isn't quite one, and most people's first attempt shows why:

SELECT dept, MAX(salary) AS top_salary
FROM employees
GROUP BY dept;

That gives the salary but not the person. Adding name to the SELECT is an error in most databases. SQLite will accept it for a single MAX, but the behavior is engine-specific and it breaks down the moment you want a top 2.

The standard pattern

Number the rows inside each group, then filter on the number.

WITH ranked AS (
  SELECT dept, name, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, id) AS n
  FROM employees
  WHERE salary IS NOT NULL
)
SELECT dept, name, salary
FROM ranked
WHERE n <= 2
ORDER BY dept, n;

PARTITION BY restarts the numbering for each department. Including id in the ORDER BY makes the result repeatable when salaries tie.

Decide what a tie means

In this data, support has two people on 61,000 and eng has two on 131,000. With ROW_NUMBER, a top-2 cut can land between two people who earn the same amount, and one of them is dropped.

If tied people should all be included, use DENSE_RANK:

WITH ranked AS (
  SELECT dept, name, salary,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS n
  FROM employees
  WHERE salary IS NOT NULL
)
SELECT dept, name, salary
FROM ranked
WHERE n <= 2
ORDER BY dept, n, name;

Compare the eng rows in the two results. In an interview, say out loud which behavior you're choosing, because the question usually doesn't specify and the interviewer wants to see that you noticed.

For more on how the three ranking functions differ, see ROW_NUMBER vs RANK vs DENSE_RANK.