SQL - Select highest value when data across 3 tables

Question

I have 3 tables:

1. Person (with a column PersonKey)
2. Telephone (with columns Tel_NumberKey, Tel_Number, Tel_NumberType e.g. 1=home, 2=mobile)
3. xref_Person+Telephone (columns PersonKey, Tel_NumberKey, CreatedDate, ModifiedDate)

I'm looking to get the most recent (e.g. the highest Tel_NumberKey) from the xref_Person+Telephone for each Person and use that Tel_NumberKey to get the actual Tel_Number from the Telephone table.

The problem I am having is that I keep getting duplicates for the same Tel_NumberKey. I also need to be sure I get both the home and mobile from the Telephone table, which I've been looking to do via 2 individual joins for each Tel_NumberType - again getting duplicates.

Been trying the following but to no avail:

-- For HOME
SELECT 
    p.PersonKey, pn.Phone_Number, pn.Tel_NumberKey
FROM 
    Persons AS p 
INNER JOIN 
    xref_Person+Telephone AS x ON p.PersonKey = x.PersonKey
INNER JOIN 
    Telephone AS pn ON x.Tel_NumberKey = pn.Tel_NumberKey
WHERE 
    pn.Tel_NumberType = 1 -- e.g. Home phone number
    AND pn.Tel_NumberKey = (SELECT MAX(pn1.Tel_NumberKey) AS Tel_NumberKey
                            FROM Person AS p1  
                            INNER JOIN xref_Person+Telephone AS x1 ON p1.PersonKey = x1.PersonKey
                            INNER JOIN Telephone AS pn1 ON x1.Tel_NumberKey = pn1.Tel_NumberKey
                            WHERE pn1.Tel_NumberType = 1
                              AND p1.PersonKey = p.PersonKey
                              AND pn1.Tel_Number = pn.Tel_Number)
ORDER BY 
    p.PersonKey

And have been looking over the following links but again keep getting duplicates.

SQL select max(date) and corresponding value

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

SQL Server: SELECT only the rows with MAX(DATE)

Am sure this must be possible but been at this a couple of days and can't believe its that difficult to get the most recent / highest value when referencing 3 tables. Any help greatly appreciated.

Answer 1

select * 
from 
( SELECT p.PersonKey, pn.Phone_Number, pn.Tel_NumberKey 
       , row_number() over (partition by p.PersonKey, pn.Phone_Number order by pn.Tel_NumberKey desc) rn
  FROM 
      Persons AS p 
  INNER JOIN 
      xref_Person+Telephone AS x ON p.PersonKey = x.PersonKey
  INNER JOIN 
      Telephone AS pn ON x.Tel_NumberKey = pn.Tel_NumberKey
  WHERE 
      pn.Tel_NumberType = 1 
) tt 
where tt.rn = 1  
ORDER BY 
    tt.PersonKey

Answer 2

you have to use max() function and then you have to order by rownum in descending order like.

select  f.empno 
from(select max(empno) empno from emp e 
group by rownum)f
order by rownum desc

It will give you all employees having highest employee number to lowest employee number. Now implement it with your case then let me know.