如何在单个表中获取每组键值的最大行？

I have an MSSQL 2000 table that has a lot of duplicate entries. Each row has an EffectiveChange data column. I want to get the most up to date row by getting the row with the max(EffectiveChange) for each key value.

我有一个MSSQL 2000表,有很多重复的条目。每行都有一个EffectiveChange数据列。我希望通过获取每个键值的max(EffectiveChange)行获得最新的行。

This is some sample data:

这是一些示例数据:

NPANXX TZONE EFFCHANGE RATE
555555 1     01/01/09   1
555555 1     05/01/09   6
214555 2     01/01/09   1
214555 2     05/01/09   3
657555 3     05/01/09   1
657555 1     01/01/09   1

I came up with this:

我想出了这个:

SELECT DISTINCT
  NPANXX,
  TZONE,
  RATE
FROM AreaCodes
INNER JOIN (SELECT DISTINCT NPANXX, EFFCHANGE FROM AREACODES) b
  ON b.NPANXX = AreaCodes.NPANXX
GROUP BY 
  NPANXX, 
  TZONE, 
  RATE 
HAVING AreadCodes.EFFCHANGE = max(b.EFFCHANGE)

My question is whether or not this query will give me the max EFFCHANGE row for each key (NPANXX) or will it only give me rows having the MAX(EFFCHANGE) for the whole table?

我的问题是这个查询是否会给我每个键的最大EFFCHANGE行(NPANXX),还是只给我整个表的MAX(EFFCHANGE)行?

1 个解决方案

#1

one way since you are using 2000 in 2005 and up you can also use row_number()

自从你在2005年使用2000以来,你可以使用row_number()

SELECT t1.* 
from AreaCodes t1
INNER JOIN (SELECT NPANXX, max(EFFCHANGE) as MaxDate FROM AREACODES
group by NPANXX) t2 
  ON t1.NPANXX = t2.NPANXX
and t1.EFFCHANGE = t2.MaxDate

here is the complete code including DML and DDL

这是完整的代码,包括DML和DDL

create table AreaCodes(NPANXX int,TZONE int,EFFCHANGE datetime,RATE int)

insert AreaCodes values(555555,1,'20090101',1)
insert AreaCodes values(555555,1,'20090501',6)
insert AreaCodes values(214555,2,'20090101',1)
insert AreaCodes values(214555,2,'20090501',3)
insert AreaCodes values(657555,3,'20090501',1)
insert AreaCodes values(657555,1,'20090101',1)


SELECT t1.* 
from AreaCodes t1
INNER JOIN (SELECT NPANXX, max(EFFCHANGE) as MaxDate FROM AREACODES
group by NPANXX) t2 
  ON t1.NPANXX = t2.NPANXX
and t1.EFFCHANGE = t2.MaxDate



output
657555  3   2009-05-01 00:00:00.000 1
555555  1   2009-05-01 00:00:00.000 6
214555  2   2009-05-01 00:00:00.000 3

#1