How to get the maximum row for each group of key values in a single table?

Question

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.

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?

Answer 1

one way since you are using 2000 in 2005 and up you can also use 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

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