Top N Rows per Group in SQL: Compare Database Syntax

To return the highest or newest N rows within each group, number rows inside each partition, then filter those numbers in an outer query. ROW_NUMBER(), RANK(), and DENSE_RANK() support different tie rules; a global LIMIT or TOP alone only limits the entire result, not each group.

Portable window-function pattern

This pattern works on current versions of MySQL, MariaDB, PostgreSQL, SQL Server, SQLite, and Oracle that support window functions:

WITH ranked AS (
    SELECT
        department,
        employee_id,
        employee_name,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC, employee_id
        ) AS row_num
    FROM employees
)
SELECT department, employee_id, employee_name, salary
FROM ranked
WHERE row_num <= 2
ORDER BY department, row_num;

PARTITION BY restarts the numbering in each department. The unique employee_id tie-breaker makes the exact two selected rows repeatable when salaries tie. The outer ORDER BY sorts the final result; it doesn’t change which rows receive each number.

Choose how ties should behave

• ROW_NUMBER() <= N returns at most N rows from each group. Add a unique tie-breaker to the window’s ORDER BY when you need a repeatable selection.
• RANK() <= N includes every row tied at the Nth rank, so a group can return more than N rows. Ranks after a tie have gaps.
• DENSE_RANK() <= N returns rows from the top N distinct ordering values, with no gaps between ranks.

For example, if several employees share the second-highest salary, RANK() <= 2 includes all of them. If exactly two employees should be selected, use ROW_NUMBER() with a unique tie-breaker.

This example shows how ties change the rank values. Assume all four employees are in the same department and request the top three rows:

With N = 3, ROW_NUMBER() <= 3 and RANK() <= 3 return employees 1–3, while DENSE_RANK() <= 3 also returns employee 4 because there are three distinct salary values. PostgreSQL’s window-function reference defines RANK() with gaps and DENSE_RANK() without gaps.

MySQL 5.7 and other older versions

MySQL 5.7 doesn’t support window functions. The MySQL guide includes a correlated-subquery alternative and explains its trade-offs. Check the minimum version and syntax for the target engine before using a window-based query.

For detailed database-specific examples, follow the guides in the table above. Browse the cross-database SQL comparison hub for other tasks where syntax or behavior differs by engine.