Identifying repeated fields in SQL query

Question

I have an SQL query that returns a column like this:

foo
-----------
1200
1200
1201
1200
1200
1202
1202
1202

It has already been ordered in a specific way, and I would like to perform another query on this result set to ID the repeated data like this:

foo    ID
----   ----
1200   1
1200   1
1201   2
1200   3
1200   3
1202   4
1202   4
1202   4

It's important that the second group of 1200 is identified as separate from the first. Every variation of OVER/PARTITION seems to want to lump both groups together. Is there a way to window the partition to only these repeated groups?

Edit: This is for Microsoft SQL Server 2012

Answer 1

Not sure this will be the fastest results...

select main.num, main.id from
(select x.num,row_number() 
over (order by (select 0)) as id 
from (select distinct num from num) x) main
join 
(select num, row_number() over(order by (select 0)) as ordering
 from num) x2 on 
x2.num=main.num
order by x2.ordering

Assuming the table "num" has a column "num" that contains your data, in the order-- of course num could be made into a view or a "with" for your original query.

Please see the following sqlfiddle

Answer 2

Here is one way to do this without a CURSOR

-- Create a temporay table
DECLARE @table TABLE 
(
    SeqID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    foo INT,    
    id int null 
)


DECLARE @i INT
DECLARE @j INT
DECLARE @k INT
declare @tFoo INT
declare @oldFoo INT

SET @k = 0
set @oldFoo = 0



-- Insert data into the temporary table
INSERT INTO @table(foo) 
SELECT 1200
INSERT INTO @table(foo) 
SELECT 1200
INSERT INTO @table(foo) 
SELECT 1201
INSERT INTO @table(foo) 
SELECT 1200
INSERT INTO @table(foo) 
SELECT 1200
INSERT INTO @table(foo) 
SELECT 1202
INSERT INTO @table(foo) 
SELECT 1202
INSERT INTO @table(foo) 
SELECT 1202

-- Get the max and min SeqIDs to loop through
SELECT @i = MIN(SeqID) FROM @table
SELECT @j = MAX(SeqID) FROM @table


-- Loop through the temp table using the SeqID indentity column
WHILE (@i <= @j)
BEGIN
  SELECT @tFoo = foo FROM @table WHERE SeqID = @i

  if @oldFoo <> @tFoo
    set @k = @k + 1 

  update @table set id = @k where SeqID = @i    

  SET @oldFoo = @tFoo
  -- Increment the counter
  SET @i = @i + 1
END

SELECT * from @table