求行中的最大值sql语句,,,,

[yiminggw]

数据库的表(table1)类似下面的

sfzh zkzh fs1
320321 123 12
320321 124 15
320322 125 13
320322 126 14

sfzh(身份证号),zkzh(准考证号),fs(分数)
要求取出 所有 相同身份证号下的分数的最大值
我取只能取出一条-_-
select sfzh,zkzh,fs1 from table1 where ?

谢谢

[Yang_]

select sfzh,zkzh,fs1
from table1 a
where zkzh=(
select top 1 zkzh from table1
where sfzh=a.sfzh
order by fs1 desc
)

[eddy8863]

select * from table1 a
where not exists(select 1 from table1 where fs>a.fs and sfzh=a.sfzh)

[eddy8863]

declare @t table (sfzh varchar(10),zkzh varchar(10),fs int)
insert @t select '320321','123',12
insert @t select '320321','124',15
insert @t select '320322','125',13
insert @t select '320322','126',14

select * from @t a
where not exists(select 1 from @t where fs>a.fs and sfzh=a.sfzh)

------------------
sfzh zkzh fs
320321 124 15
320322 126 14

[huailairen]

create table table1(sfzh varchar(10), zkzh varchar(10), fs1 int )

insert into table1 values('320321', '123', 12 )
insert into table1 values('320321', '124', 15 )
insert into table1 values('320322', '125', 13)
insert into table1 values('320322' , '126' , 14)


select sfzh,zkzh,fs1
from table1 a
where not exists(select * from table1 where a.fs1<table1.fs1 and a.sfzh=table1.sfzh )

[huailairen]

结果
sfzh zkzh fs1
---------- ---------- -----------
320321 124 15
320322 126 14

（所影响的行数为 2 行）

[yiminggw]

o,多谢大家撒:)