IF EXISTS(SELECT name FROM sysobjects WHERE name = N'T' AND type = 'U') DROP TABLE TGOCREATE TABLE T (C1 INT, C2 CHAR(8),C3 CHAR(8))GOINSERT INTO TSELECT 1,'AA','K' UNION ALLSELECT 2,'AA','L' UNION ALLSELECT 3,'BB','K' UNION ALLSELECT 4,'BB','L' UNION ALLSELECT 5,'CC','K' UNION ALLSELECT 6,'CC','L' UNION ALLSELECT 7,'DD','K' UNION ALLSELECT 8,'DD','L' UNION ALLSELECT 9,'EE','K' UNION ALLSELECT 10,'FF','L'goselect * from tgoselect C1,CD=(select Case C2 when 'AA' then 10 when 'BB' then 20 when 'CC' then 30 when 'DD' then 40 when 'EE' then 50 when 'FF' then 60 end from t t1 where t1.c1=t3.c1 group by C2)*10+(select Case C3 when 'K' then 1 when 'L' then 2 end from t t2 where t2.c1=t3.c1 group by C3),C2,C3 from t t3
select top 1 newid(), * from t order by newid()
