Apply Row Number Function on Distinct Value

Sometime we need to apply the Row Number function on Distinct value .
Here is the example :

Declare @Table Table (COL1 Int , COL2 Int)

Insert Into @Table
Select 100, 5000
UNION ALL
Select 101, 5000
UNION ALL
Select 102, 5000
UNION ALL
Select 100, 7000
UNION ALL
Select 102, 8000
UNION ALL
Select 103, 9000

Suppose here we want to just list all values from COL1 with a extra Row Number column .
Output should be like :

Col1	Sr.No
100	1
101	2
102	3
103	4

To generate the Sequence number we need to use Row_Number function .

---- SIMPLE
Select COL1, ROW_NUMBER() Over(Order By COL1) As RN
From @Table
---Output

COL1 RN
-----------
100 1
100 2
101 3
102 4
102 5
103 6

Here we generate the Sequence Number for COL1, ohh but what is this happening , sequence number is also generating for duplicate records means for all the records of COL1.

Lets try use the DISTINCT to get the unique values:

---- WITH Distinct
Select DISTINCT COL1, ROW_NUMBER() Over(Order By COL1) As RN
From @Table

--- Output
COL1       RN
-----------
100         1
100         2
101         3
102         4
102         5
103         6

Output is same again.

Lets give a chance to GROUP BY clause ..

---- WITH Group By
Select COL1, ROW_NUMBER() Over(Order By COL1) As RN
From @Table
Group By COL1
--- Output
COL1        RN
----------- --------------------
100         1
101         2
102         3
103         4


Wow, its working now.
Means Group By works well with Row_Number Function.


I did not try where it can fails....