问个排序去数有关问题

select distinct b.* from tb across apply (select top 1 from tb where col2=a.col2) b
------解决方案--------------------
SQL code
------------------------------ Author  :fredrickhu(小F，向高手学习)-- Date    :2011-11-30 16:07:21-- Version:--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) --    Apr 22 2011 11:57:00 --    Copyright (c) Microsoft Corporation--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([col1] varchar(1),[col2] int,[COL3] varchar(3))insert [tb]select 'C',3,'AAA' union allselect 'B',3,'BBB' union allselect null,3,'CCC' union allselect null,2,'AAA' union allselect null,2,'BBB' union allselect 'D',1,'aaa' union allselect 'B',1,'bbb'--------------开始查询--------------------------select    col1 ,col2, COL3 from   (select px=ROW_NUMBER()over(partition by col2 order by GETDATE()),* from tb)t where   px=1----------------结果----------------------------/* col1 col2        COL3---- ----------- ----D    1           aaaNULL 2           AAAC    3           AAA(3 行受影响)*/
------解决方案--------------------
如果一定要用col1,则建议使用临时表
select * ,id = identity(int,1,1) into tmp from tb

select t.* from tb t where id = (select top 1 id from tb where col2 = t.col2 order by id)

select t.* from tb t where id = (select min(id) from tb where col2 = t.col2)

------解决方案--------------------
SQL code
create table xiaol028 (col1 char(1), col2 int, COL3 char(5))insert into xiaol028select 'C', 3, 'AAA' union allselect 'B', 3, 'BBB' union allselect null, 3, 'CCC' union allselect null, 2, 'AAA' union allselect null, 2, 'BBB' union allselect 'D', 1, 'aaa' union allselect 'B', 1, 'bbb'select t.col1,t.col2,t.col3from (select row_number() over(partition by col2 order by (select 0)) rn,col1,col2,col3from xiaol028) twhere t.rn=1order by t.col2 desccol1 col2        col3---- ----------- -----C    3           AAA  NULL 2           AAA  D    1           aaa  (3 row(s) affected)