Sql server® 2012 t-sql fundamentals

 

CHAPTER 7

 

Beyond the Fundamentals of Querying

 

 217

The ROW_NUMBER function is processed before the DISTINCT clause. First, unique row numbers 

are assigned to the 830 rows from the OrderValues view. Then the DISTINCT clause is processed—

therefore, there are no duplicate rows to remove. You can consider it a best practice not to specify 

both DISTINCT and ROW_NUMBER in the same SELECT clause, because the DISTINCT clause has no 

effect in such a case. If you want to assign row numbers to the 795 unique values, you need to come 

up with a different solution. For example, because the GROUP BY phase is processed before the SELECT 

phase, you could use the following query.

SELECT val, ROW_NUMBER() OVER(ORDER BY val) AS rownum 

FROM Sales.OrderValues 

GROUP BY val;

This query generates the following output, shown here in abbreviated form.

val       rownum 

--------- ------- 

12.50     1 

18.40     2 

23.80     3 

28.00     4 

30.00     5 

33.75     6 

36.00     7 

40.00     8 

45.00     9 

48.00     10 

... 

12615.05  793 

15810.00  794 

16387.50  795 

 

(795 row(s) affected) 

Here, the GROUP BY phase produces 795 groups for the 795 distinct values, and then the SELECT 

phase produces a row for each group with val and a row number based on val order.