create table test(cid int,addr nvarchar(100) null,isdefault int null)
insert into test select 1,'aaa',null
union select 1,'bbb',1
union select 2,'ccc',1
union select 2,'ddd',1
union select 2,'eee',0
union select 2,'fff',1
go
;with AcHerat as
(
select *,rid=row_number() over (partition by cid order by (case when isdefault = 1 then 0
when isdefault = 0 then 1 else 2 end))
from test
)
select *
from AcHerat
where rid = 1
drop table test
/****************
cid addr isdefault rid
----------- ---------------------------------------------------------------------------------------------------- ----------- --------------------
1 bbb 1 1
2 fff 1 1
(2 行受影响)
