You have a products table with columns category, product_id, and price. You want to find the top 2 most expensive products per category, but if two products have the same price, include both even if it means more than 2 per category. Which query achieves this?
AUse ROW_NUMBER() partitioned by category ordered by price DESC and filter WHERE rn <= 2.
BUse RANK() partitioned by category ordered by price DESC and filter WHERE rank <= 2.
CUse DENSE_RANK() partitioned by category ordered by price ASC and filter WHERE dense_rank <= 2.
DUse COUNT() partitioned by category ordered by price DESC and filter WHERE count <= 2.
Step-by-Step Solution
Solution:
Step 1: Understand difference between ROW_NUMBER(), RANK(), and DENSE_RANK()
ROW_NUMBER() assigns unique ranks, so ties get different numbers. RANK() assigns same rank to ties and skips ranks after. DENSE_RANK() assigns same rank to ties but no gaps.
Step 2: Choose RANK() to include ties at second place
RANK() allows including all products tied at rank 2, possibly more than 2 rows per category.
Final Answer:
Use RANK() partitioned by category ordered by price DESC and filter WHERE rank <= 2. -> Option B
Quick Check:
RANK() includes ties with same rank [OK]
Quick Trick:Use RANK() to include ties in top N per group [OK]
Common Mistakes:
Using ROW_NUMBER() excludes ties
Using DENSE_RANK() includes ties but no gaps
Using COUNT() is invalid for ranking
Master "Advanced Query Patterns" in SQL
9 interactive learning modes - each teaches the same concept differently
