declare @t table(A int,B varchar(10),C varchar(10)) insert @t select 200, 'A1', 'F' union all select 300, 'A2', 'F' union all select 400, 'A3', 'D' union all select 100, 'A4', 'D'select B from @t where C = 'F' and A = (select max(A) from @t where C = 'F')
select t.* from tablename t,(select max(a)a,c from tablename where c='f' group by c) x where t.a=x.a and t.c=x.c
if ((select count(*) from sysobjects where name = 'table1') > 0) drop table table1 create table table1(A int, B varchar(20), C varchar(20)) insert into table1 select 200, 'A1', 'F' union select 300, 'A3', 'F' union select 400, 'A3', 'D' union select 100, 'A4', 'D' select B from table1 where A = (select max(A) from table1 where C = 'F')
insert @t
select 200, 'A1', 'F' union all
select 300, 'A2', 'F' union all
select 400, 'A3', 'D' union all
select 100, 'A4', 'D'select B from @t where C = 'F' and A = (select max(A) from @t where C = 'F')
drop table table1
create table table1(A int, B varchar(20), C varchar(20))
insert into table1
select 200, 'A1', 'F' union
select 300, 'A3', 'F' union
select 400, 'A3', 'D' union
select 100, 'A4', 'D'
select B from table1 where A = (select max(A) from table1 where C = 'F')