问个排序去数问题

[xiaol028]

col1 col2 COL3
C 3 AAA
B 3 BBB
NULL 3 CCC
NULL 2 AAA
NULL 2 BBB
D 1 aaa
B 1 bbb


我想得到的是以col2分组 取col1的第一个
col1 col2 col3
C 3 AAA
NULL 2 AAA
D 1 aaa

[快溜]

select distinct b.* from tb a
cross apply (select top 1 from tb where col2=a.col2) b

[快溜]

select distinct b.* from tb a
cross apply (select top 1 * from tb where col2=a.col2) b

[--小F--]

----------------------------------------------------------------
-- 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 all
select 'B',3,'BBB' union all
select null,3,'CCC' union all
select null,2,'AAA' union all
select null,2,'BBB' union all
select 'D',1,'aaa' union all
select '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           aaa
NULL 2           AAA
C    3           AAA

(3 行受影响)
*/

[dawugui]

--你的col1存在null,建议使用col3

select t.* from tb t where col3 = (select top 1 col3 from tb where col2 = t.col2)

[dawugui]

如果一定要用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)

[xiaol028]

是一定要用col1 col3 意义不大，只是写出来便于区别

[xiaol028]

CROSS APPLY 要sql server2005 以上版本吧， 用的2005 好像不支持

[xiaol028]

[Quote=引用 3 楼 fredrickhu 的回复:]
SQL code

----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2011-11-30 16:07:21
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM)……
[/Quote]

实际数据测试好像不行，但是在分组排序用倒叙能行，我在试试其他的数据呢

[唐诗三百首]

create table xiaol028 (col1 char(1), col2 int, COL3 char(5))

insert into xiaol028
select 'C', 3, 'AAA' union all
select 'B', 3, 'BBB' union all
select null, 3, 'CCC' union all
select null, 2, 'AAA' union all
select null, 2, 'BBB' union all
select 'D', 1, 'aaa' union all
select 'B', 1, 'bbb'

select t.col1,t.col2,t.col3
from 
(select row_number() over(partition by col2 order by (select 0)) rn,
col1,col2,col3
from xiaol028) t
where t.rn=1
order by t.col2 desc

col1 col2        col3
---- ----------- -----
C    3           AAA  
NULL 2           AAA  
D    1           aaa  

(3 row(s) affected)