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 
*/

This page was last updated on May 01, 2006 04:28 PM.