Explain the differences between ROW_NUMBER, RANK, and DENSE_RANK window functions.
💡 Model Answer
ROW_NUMBER, RANK, and DENSE_RANK are all window functions that assign a unique number to each row within a partition, but they differ in how they handle ties.
• ROW_NUMBER() assigns a unique sequential number to each row, regardless of duplicates. If two rows tie on the ORDER BY clause, they still receive distinct numbers.
• RANK() assigns the same rank to tied rows, but leaves gaps in the ranking sequence. For example, if two rows tie for rank 1, the next distinct row receives rank 3.
• DENSE_RANK() also assigns the same rank to tied rows, but does not leave gaps. Using the same example, the next distinct row would receive rank 2.
These functions are useful for pagination, ranking, and generating unique identifiers within partitions.
This answer was generated by AI for study purposes. Use it as a starting point — personalize it with your own experience.
🎤 Get questions like this answered in real-time
Assisting AI listens to your interview, captures questions live, and gives you instant AI-powered answers — invisible to screen sharing.
Get Assisting AI — Starts at ₹500