In a T-SQL statement ranking specific customers by SalesAmount with ties, which function should you use for the Ranking column to ensure proper tie handling?

In a T-SQL statement ranking specific customers by SalesAmount with ties, which function should you use for the Ranking column to ensure proper tie handling?

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

Answer: (A)

When ranking by a value with ties, you want equal values to share the same rank and for the next distinct value to have the next consecutive rank. DENSE_RANK does exactly that: it assigns the same rank to all rows with the same SalesAmount, and the subsequent distinct amount gets the next number without gaps. For example, if two customers tie for the top SalesAmount, both will be ranked 1, the next distinct amount will be ranked 2, and so on. This contrasts with RANK, which would leave gaps after ties (e.g., 1, 1, then 3), ROW_NUMBER, which assigns unique sequential numbers regardless of ties, and NTILE, which partitions rows into equal groups rather than ranking by value. So, to handle ties correctly and keep ranks dense, use DENSE_RANK.

When ranking by a value with ties, you want equal values to share the same rank and for the next distinct value to have the next consecutive rank. DENSE_RANK does exactly that: it assigns the same rank to all rows with the same SalesAmount, and the subsequent distinct amount gets the next number without gaps. For example, if two customers tie for the top SalesAmount, both will be ranked 1, the next distinct amount will be ranked 2, and so on. This contrasts with RANK, which would leave gaps after ties (e.g., 1, 1, then 3), ROW_NUMBER, which assigns unique sequential numbers regardless of ties, and NTILE, which partitions rows into equal groups rather than ranking by value. So, to handle ties correctly and keep ranks dense, use DENSE_RANK.