Avoid duplicates on phn_nbr while joining a table to itself

[aft5425]

Using Informix database 11.50.FC3 on an AIX UNIX (o/s 6.1) server.

Table has 3 records with the following columns. Two of the records have same phn_nbr and time_stmp:
record_type source_chnl time_stmp phn_nbr phn_sts frst_nm mi lst_nm
DTL 100040 2013-05-22 10:19:55.000 0004164931 A Ann Z Brenner
DTL 100040 2013-05-22 10:19:55.000 0004164931 A J W W Brenner
DTL 100040 2013-05-22 10:19:55.000 0004169559 A Gerard R Jarvis

here is my query:
SELECT DISTINCT A.record_type,
A.source_chnl,
A.time_stmp,
A.phn_nbr,
A.phn_sts,
A.frst_nm,
A.mi,
A.lst_nm
FROM DNC_CNSMR_HUB A INNER JOIN (
SELECT DISTINCT phn_nbr,
MAX(time_stmp) ts
FROM DNC_CNSMR_HUB
WHERE phn_nbr IS NOT NULL
AND phn_nbr <> ' '
GROUP BY phn_nbr
) B ON A.phn_nbr = B.phn_nbr AND A.time_stmp = B.ts

When i execute the SQL for just the sub-select (inner joined table) i get 2 rows:
0004164931 2013-05-22 10:19:55.000
0004169559 2013-05-22 10:19:55.000

But when i run the entire query, i get 3 rows because phn_nbr and time_stmp are the same on two records. I was using the time_stmp to get distinct phn_nbr, but as you can see, when the time_stmp is the same, i get both records in the result set. How can i avoid duplicates on phn_nbr? I don't care which record on the 2 dups i get because it's the same household anyway.

Result Set:
record_type source_chnl time_stmp phn_nbr phn_sts frst_nm mi lst_nm
DTL 100040 2013-05-22 10:19:55.000 0004164931 A Ann Z Brenner
DTL 100040 2013-05-22 10:19:55.000 0004164931 A J W W Brenner
DTL 100040 2013-05-22 10:19:55.000 0004169559 A Gerard R Jarvis

 

[PHV]

Perhaps something like this ?
SELECT A.record_type,
A.source_chnl,
A.time_stmp,
A.phn_nbr,
A.phn_sts,
A.frst_nm,
A.mi,
A.lst_nm
FROM DNC_CNSMR_HUB A INNER JOIN (
SELECT phn_nbr, MAX(time_stmp) ts, MAX(frst_nm||mi||lst_nm) FullName
FROM DNC_CNSMR_HUB WHERE phn_nbr <> ' '
GROUP BY phn_nbr
) B ON A.phn_nbr = B.phn_nbr AND A.time_stmp = B.ts
WHERE A.frst_nm||A.mi||lA.st_nm = B.FullName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886