get records with distinct phone numbers only

[aft5425]

using informix db 11.50.FC3
AIX Unix server 6.1 O/S

i have a table with record type, name, timestamp, status and phone number; there can be multiple records with the same phone number; i want a result set that does not have the same phone number on multiple records; if there are two records with the same phone number in the table, only the record with the latest timestamp should be in the result set; dups on other fields in the result set are ok

Data:
record type name timestamp status phone number
DTL Mark 4/1/13 11:16 A 5557778888
DTL Mark 4/2/13 13:14 A 5557778888
DTL John 4/5/13 09:53 A 5559996666

I want the result set to be:
record type name timestamp status phone number
DTL Mark 4/2/13 13:14 A 5557778888
DTL John 4/5/13 09:53 A 5559996666

My query is:
select distinct record_type, name, timestamp, status, phn_nbr
from Table1
where phn_nbr in
(select distinct phn_nbr from Table1 where phn_nbr is not null)

...but this still gives me record # 1 in the result set.

What am i doing wrong? Is there another key word besides DISTINCT that i could use?

 

[PHV]

I'd try something like this:

SELECT A.record_type, A.name, A.timestamp, A.status, A.phn_nbr 
FROM Table1 A INNER JOIN (
SELECT phn_nbr, MAX(timestamp) ts FROM Table1 WHERE phn_nbr IS NOT NULL GROUP BY phn_nbr
) B ON A.phn_nbr = B.phn_nbr AND A.timestamp = B.ts

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

 

[aft5425]

Thanks so much. I got this to work. Initially i didn't think the inner join wold work because i had trouble joining a table to itself, but using your example, i can see where i made a mistake.