Which function should be used to rank results without gaps when there are ties in SQL?

Which function should be used to rank results without gaps when there are ties in SQL?

• A. DENSE_RANK()
• B. NTILE()
• C. RANK()
• D. ROW_NUMBER()

Answer: (A)

When you need to rank results and want ties to share the same rank while keeping subsequent ranks consecutive, you use a dense ranking approach. Dense_RANK assigns the same rank to all rows with equal values and gives the next different value the next integer rank, with no gaps. For example, with scores 100, 95, 95, 90, 85, the ranks would be 1, 2, 2, 3, 4. RANK also gives the same rank to ties but leaves gaps after a tie, so you’d see 1, 2, 2, 4, 5. ROW_NUMBER assigns a unique, consecutive number to every row regardless of ties, which doesn’t reflect rankings with ties. NTILE distributes rows into a specified number of buckets rather than producing a true rank sequence. Dense_RANK is the one that preserves consecutive ranking without gaps when ties occur.

When you need to rank results and want ties to share the same rank while keeping subsequent ranks consecutive, you use a dense ranking approach. Dense_RANK assigns the same rank to all rows with equal values and gives the next different value the next integer rank, with no gaps. For example, with scores 100, 95, 95, 90, 85, the ranks would be 1, 2, 2, 3, 4.

RANK also gives the same rank to ties but leaves gaps after a tie, so you’d see 1, 2, 2, 4, 5. ROW_NUMBER assigns a unique, consecutive number to every row regardless of ties, which doesn’t reflect rankings with ties. NTILE distributes rows into a specified number of buckets rather than producing a true rank sequence. Dense_RANK is the one that preserves consecutive ranking without gaps when ties occur.