SQL 分组查询问题

[yingziming]

字段1 字段2 字段3
25785 K144 2012-07-01 00:00:00.000
25787 K144 2012-08-01 00:00:00.000
25789 K144 2012-08-01 00:00:00.000
25790 L155 2012-07-01 00:00:00.000
25791 L155 2012-09-01 00:00:00.000
25793 D139 2012-07-01 00:00:00.000
26201 D139 2011-11-01 00:00:00.000
32761 D139 2013-07-01 00:00:00.000

字段1是唯一的，字段2 如，K144是一组数据，L155 ，D139 ，我要找到每组时间最大数据
查询最新数据，结果如下
25787 K144 2012-08-01 00:00:00.000
25789 K144 2012-08-01 00:00:00.000
25791 L155 2012-09-01 00:00:00.000
26201 D139 2011-11-01 00:00:00.000

[LongRui888]

select *
from
(
select *,
       ROW_NUMBER() over(partition by 字段2 order by 字段3 desc) rownum
from tb
)t
where rownum = 1

[LongRui888]

改一下：

select *
from
(
select *,
       dense_rank() over(partition by 字段2 order by 字段3 desc) rownum
from tb
)t
where rownum = 1

[Landa_Jimmy]

create table #tab(字段1 int, 字段2 varchar(50), 字段3 datetime)
insert into #tab

select 25785, 'K144', '2012-07-01 00:00:00.000' union all
select 25787, 'K144', '2012-08-01 00:00:00.000' union all
select 25789, 'K144', '2012-08-01 00:00:00.000' union all
select 25790, 'L155', '2012-07-01 00:00:00.000' union all
select 25791, 'L155', '2012-09-01 00:00:00.000' union all
select 25793, 'D139', '2012-07-01 00:00:00.000' union all
select 26201, 'D139', '2011-11-01 00:00:00.000' union all
select 32761, 'D139', '2013-07-01 00:00:00.000'

select * from #tab
select * from (
select row_number()over(partition by 字段2 order by 字段3 desc)id,* from #tab
)a where id=1
----------------------------------------------------------
id                   字段1         字段2                                                字段3
-------------------- ----------- -------------------------------------------------- -----------------------
1                    32761       D139                                               2013-07-01 00:00:00.000
1                    25787       K144                                               2012-08-01 00:00:00.000
1                    25791       L155                                               2012-09-01 00:00:00.000

(3 行受影响)