intermediateWindow Functions — ROW_NUMBER, RANK, LEAD, LAG
How do you find the top-N rows per group using window functions?
Use ROW_NUMBER() with PARTITION BY: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees) ranked WHERE rn <= 2. How it works: ROW_NUMBER() assigns 1 to highest salary per department, 2 to second highest, etc. The outer WHERE rn <= 2 keeps only the top 2 per department. Why ROW_NUMBER over RANK: if two employees tie at the same salary, RANK wou
Ready to master this question?
Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.
Sign in to generate a response