一个简单的SQL语法请教大家
假设有一个表：
declare @t table(type nvarchar(30),i smallint,s1 nvarchar(50),s2 nvarchar(50))
insert @t
select 'A ',1, 'hh ', 'xx '
union all
select 'A ',2, 'jj ', 'ww '
union all
select 'B ',2, 'nn ', 'bb '
union all
select 'A ',3, 'b ', 'j '
union all
select 'B ',1, 'p ', 'h '
现在可通过如下语句得到每个type中最大的ID：
select type,max(i) as MaxID from @t group by type
结果是：
type MaxID
A 3
B 2
可是，我想不仅得到MaxID，而要得到MaxID所对应行的其他列的信息：如下：
type maxid str1 str2
A 3 'b ' 'j '
B 2 'nn ' 'bb '
请问SqL语句如何表达？谢谢！
[解决办法]
declare @t table(type nvarchar(30),i smallint,s1 nvarchar(50),s2 nvarchar(50))
insert @t
select 'A ',1, 'hh ', 'xx '
union all
select 'A ',2, 'jj ', 'ww '
union all
select 'B ',2, 'nn ', 'bb '
union all
select 'A ',3, 'b ', 'j '
union all
select 'B ',1, 'p ', 'h '
select t.* from @t t where t.i=(select max(i) from @t where type=t.type)
[解决办法]
select *
from @t as a
where i=(select max(i) from @t where type=a.type)
[解决办法]
select * from @t a where NOT exists (select 1 from @t where a.type=type and a.I <I)
[解决办法]
declare @t table(type nvarchar(30),i smallint,s1 nvarchar(50),s2 nvarchar(50))
insert @t
select 'A ',1, 'hh ', 'xx '
union all
select 'A ',2, 'jj ', 'ww '
union all
select 'B ',2, 'nn ', 'bb '
union all
select 'A ',3, 'b ', 'j '
union all
select 'B ',1, 'p ', 'h '
select type,max(i)as max_i into #t from @t group by type
select * from #t
select * from @t a,#t b
where a.type=b.type and a.i=b.max_i
drop table #t
