/* Author Rob Volk

Generate Sequence Order By Group

COLUMN_1	SEQUENCE_ID
--------	-----------
A			NULL
A			NULL
B			NULL
B			NULL
B			NULL
C			NULL
C			NULL
C			NULL
C			NULL


What I want is the #temp table to look like this: 


COLUMN_1	SEQUENCE_ID
--------	-----------
A			1
A			2
B			1  <- The sequence_id needs to be reset
B			2     to 1 when the row value in COLUMN_1
B			3     changes
C			1
C			2
C			3
C			4


Seing from the above, the sequencing for SEQUENCE_ID is by COLUMN_1 aggregation. 

*/

DECLARE @col1 varchar(1), @seq int 
SELECT @seq=0 
UPDATE #temp 
SET @seq = SEQUENCE_ID = CASE @col1 WHEN COLUMN_1 THEN @seq+1 ELSE 1 END, 
@col1 = COLUMN_1 

/*
Author Onamuji
*/
CREATE TABLE Sequences (Value INT NOT NULL PRIMARY KEY) 

WHILE (SELECT ISNULL(MAX(Value), 0) FROM Sequences) < 8192 
INSERT INTO Sequences SELECT ISNULL(MAX(Value), -1) + 1 FROM Sequences

CREATE TABLE #temp (Column_1 CHAR(1), Sequence_ID INT) 

INSERT INTO #temp (Column_1) VALUES ('A') 
INSERT INTO #temp (Column_1) VALUES ('A') 
INSERT INTO #temp (Column_1) VALUES ('B') 
INSERT INTO #temp (Column_1) VALUES ('B') 
INSERT INTO #temp (Column_1) VALUES ('B') 
INSERT INTO #temp (Column_1) VALUES ('C') 
INSERT INTO #temp (Column_1) VALUES ('C') 
INSERT INTO #temp (Column_1) VALUES ('C') 
INSERT INTO #temp (Column_1) VALUES ('C') 

SELECT totals.Column_1, seq.Value 
FROM (SELECT Column_1, COUNT(*) AS TotalCount FROM #temp GROUP BY Column_1) AS totals 
INNER JOIN Sequences AS seq ON seq.Value <= totals.TotalCount AND seq.Value > 0 



DROP TABLE #Temp
DROP TABLE Sequences 

quote:

---

The auto-incrment in the select would be most useful in selecting the top 20 of a grouping (top 20 for each day ...) without using temp tables / table vars or cursors.

---