Forgly

SQL to get the top n rows per group

Return the highest N rows within each group (greatest-n-per-group).

PostgreSQL, MySQL 8+, SQL Server, SQLite 3.25+
SELECT *
FROM (
  SELECT employee_id,
         department_id,
         salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rn
  FROM employees
) ranked
WHERE rn <= 3;  -- top 3 per department

How to select the top N rows per group

To get, say, the top 3 earners in every department, number rows within each group and keep the first N.

How it works

  • PARTITION BY department_id restarts the numbering for each department.
  • ORDER BY salary DESC puts the highest earner at rn = 1 within each department.
  • WHERE rn <= 3 keeps the top three per group; change 3 to any N.
  • Use RANK() instead of ROW_NUMBER() if you want to include ties at the cut-off.

Related SQL queries

Frequently asked questions

How do I select the top N rows per group in SQL?

To get, say, the top 3 earners in every department, number rows within each group and keep the first N. PARTITION BY department_id restarts the numbering for each department. ORDER BY salary DESC puts the highest earner at rn = 1 within each department. WHERE rn <= 3 keeps the top three per group; change 3 to any N. Use RANK() instead of ROW_NUMBER() if you want to include ties at the cut-off.

Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?

Yes — this query uses standard SQL that runs unchanged on PostgreSQL, MySQL, SQL Server, and SQLite.

Can I generate this query for my own tables?

Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.