mssql sql server获取表中最大列所在的行数据


摘要:
我们经常会接收到用户以下需求:
sql取最早的一条数据
取某一字段分组后,最大值所在行的数据
SQL查询一个表中类别字段中Max()最大值对应的记录
sql表中如何获得最大时间的记录
取一组数据中,最大的一行数据
等等这些需求。
下文将讲述 如何获取记录中最大值

create table A(info1 varchar(300),keyId int,otherInfo varchar(200))
insert into A values('a',1,'one')
insert into A values('a',2,'two')
insert into A values('a',3,'three')
insert into A values('b',1,'four')
insert into A values('b',1,'five')
insert into A values('b',2,'six')
insert into A values('b',2,'seven')
insert into A values('b',2,'eight')
go

--方法1:
select a.* from A a,(select info1,max(keyId) keyId from A group by info1) b where a.info1 = b.info1 and a.keyId = b.keyId order by a.info1
--方法2:
select a.* from A a inner join (select info1 , max(keyId) keyId from A group by info1) b on a.info1 = b.info1 and a.keyId = b.keyId order by a.info1
 
 ---方法3 每个群组里面取最大值(只返回一行数据)
select a.info1,a.keyId,a.otherinfo from
(
  select * , keyId2 = row_number() over(order by info1 , keyId) from A
) a where keyId2 = (select max(keyId3) from
(
  select * , keyId3 = row_number() over(order by info1 , keyId) from A
) b where a.info1 = b.info1)


go

truncate table A
drop     table A 
go