SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM ( SELECT sch_code='code1', qual_no=18, dsp_seq=null UNION ALL SELECT 'code1', 5, null UNION ALL SELECT 'code1', 4, null UNION ALL SELECT 'code1', 3, null UNION ALL SELECT 'code2', 1, null UNION ALL SELECT 'code2', 11, null UNION ALL SELECT 'code2', 21, null UNION ALL SELECT 'code3', 50, null UNION ALL SELECT 'code3', 51, null UNION ALL SELECT 'code3', 52, null UNION ALL SELECT 'code3', 53, null UNION ALL SELECT 'code3', 54, null ) TSELECT sch_code,qual_no,dsp_seq=ID-(SELECT COUNT(*) FROM # WHERE sch_code<a.sch_code) FROM # AS ADROP TABLE # /* sch_code qual_no dsp_seq -------- ----------- ----------- code1 18 1 code1 5 2 code1 4 3 code1 3 4 code2 1 1 code2 11 2 code2 21 3 code3 50 1 code3 51 2 code3 52 3 code3 53 4 code3 54 5 */
SELECT *,ID=IDENTITY(INT,1,1) INTO #
FROM (
SELECT sch_code='code1', qual_no=18, dsp_seq=null UNION ALL
SELECT 'code1', 5, null UNION ALL
SELECT 'code1', 4, null UNION ALL
SELECT 'code1', 3, null UNION ALL
SELECT 'code2', 1, null UNION ALL
SELECT 'code2', 11, null UNION ALL
SELECT 'code2', 21, null UNION ALL
SELECT 'code3', 50, null UNION ALL
SELECT 'code3', 51, null UNION ALL
SELECT 'code3', 52, null UNION ALL
SELECT 'code3', 53, null UNION ALL
SELECT 'code3', 54, null
) TSELECT sch_code,qual_no,dsp_seq=ID-(SELECT COUNT(*) FROM # WHERE sch_code<a.sch_code) FROM # AS ADROP TABLE #
/*
sch_code qual_no dsp_seq
-------- ----------- -----------
code1 18 1
code1 5 2
code1 4 3
code1 3 4
code2 1 1
code2 11 2
code2 21 3
code3 50 1
code3 51 2
code3 52 3
code3 53 4
code3 54 5
*/