select * from tb t where ID =(select max(ID ) from tb where PROVINCE=t.PROVINCE)
create table #TT1 ( ID int, PROVINCE varchar(30), CITY varchar(30) ) insert into #TT1 select 1,'广东','广州' insert into #TT1 select 2,'广东','深圳' insert into #TT1 select 3,'湖北','武汉'select min(ID) ID,PROVINCE from #TT1 group by PROVINCE ID PROVINCE ----------- ------------------------------ 1 广东 3 湖北(2 行受影响)
什么要求?难道是选择city是province的省会的记录?
select id,PROVINCE from [tb] a where id=(select min(id) from tb where PROVINCE=a.PROVINCE )
from tb t
where ID =(select max(ID ) from tb where PROVINCE=t.PROVINCE)
(
ID int,
PROVINCE varchar(30),
CITY varchar(30)
)
insert into #TT1 select 1,'广东','广州'
insert into #TT1 select 2,'广东','深圳'
insert into #TT1 select 3,'湖北','武汉'select min(ID) ID,PROVINCE from #TT1 group by PROVINCE
ID PROVINCE
----------- ------------------------------
1 广东
3 湖北(2 行受影响)
where ID is not 2