Running count column

Question

Hello,

I have a simple table with few column, one of the column is FK ID that (of course) can repeat it self more then one time in the table.
I want to create a Select Query that select this FK ID and base on this column will add additional column with running count start from 1 that every unique FK ID will add one more.

To keep things simple, this is my select query:

    SELECT 
            T.FK
             ------>here need the additional column with the running number
    FROM 
          Tbl T
   ORDER BY
          T.FK

The resulte I'm expecting is this:

 T.FK         Additional Col
-----------------------------
275               1
275               1
275               1
303               2
303               2
309               3
309               3
309               3
309               3
350               4
400               5
400               5

What do I need to add to my query to get this additional column ?

Answer 1

Select T.FK, Dense_Rank() Over(Order By T.FK) As NewColumn
From Tbl T;

Tom

Answer 2

Try this:

;WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER(ORDER BY FK) AS AdditionalCol
    FROM (
        SELECT FK 
        FROM Tbl
        GROUP BY FK
    ) AS t
)

SELECT T.FK, C.AdditionalCol
FROM Tbl AS T
INNER JOIN CTE AS C ON T.FK = C.FK;