Binaryworld - How to get identity kind of values for each row in a query? This techn ... [ SQL Server -> T-SQL ]

CREATE TABLE #Test (Description CHAR(5));
INSERT #Test VALUES('AAAAA');
INSERT #Test VALUES('BAAAB');
INSERT #Test VALUES('HAAAB');
INSERT #Test VALUES('MMMMM');
INSERT #Test VALUES('TAAAR');
INSERT #Test VALUES('XXXXZ');
INSERT #Test VALUES('ZZZZA');
SELECT * FROM #Test;
/*
Description
-----------
AAAAA
BAAAB
HAAAB
MMMMM
TAAAR
XXXXZ
ZZZZA
*/
GO
-- Generate sequence using CROSS JOIN. Just an example, may not be the best way always
SELECT t1.DESCRIPTION,
SUM(CASE WHEN t2.DESCRIPTION <= t1.DESCRIPTION THEN 1 ELSE 0 END) AS Sequence
FROM #Test t1 CROSS JOIN #Test t2
GROUP BY t1.DESCRIPTION
ORDER BY t1.DESCRIPTION;
/*
DESCRIPTION Sequence
----------- -----------
AAAAA         1
BAAAB         2
HAAAB         3
MMMMM         4
TAAAR         5
XXXXZ         6
ZZZZA         7
*/
-- Using a sub-query...
SELECT t1.DESCRIPTION,
(SELECT COUNT(*) FROM #Test t2 WHERE t2.DESCRIPTION <= t1.DESCRIPTION) AS Sequence
FROM #Test t1
ORDER BY t1.DESCRIPTION;
/*
DESCRIPTION Sequence
----------- -----------
AAAAA         1
BAAAB         2
HAAAB         3
MMMMM         4
TAAAR         5
XXXXZ         6
ZZZZA         7
*/