Post #2429

✅SQL Interview Challenge – Filter Top N Records per Group🧠💾 🧑‍💼 Interviewer: How would you fetch the top 2 highest-paid employees per department? 👨‍💻 Me: Use ROW_NUMBER() with a PARTITION BY clause—it's a window function that numbers rows uniquely within groups, resetting per partition for precise top-N filtering. 🔹 SQL Query: SELECT * FROM ( SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees ) AS ranked WHERE rn <= 2; ✔ Why it works: – PARTITION BY department resets row numbers (starting at 1) for each dept group, treating them as mini-tables. – ORDER BY salary DESC ranks highest first within each partition. – WHERE rn <= 2 grabs the top 2 per group—subquery avoids duplicates in complex joins! 💡Pro Tip: Swap to RANK() if ties get equal ranks (e.g., two at #1 means next is #3, but you might get 3 rows); DENSE_RANK() avoids gaps. For big datasets, this scales well in SQL Server or Postgres. 💬Tap ❤️ for more!