i got an Address Table with Primary and Secondary Company locations, example:

ADDRESSES:

 ID    CompanyName       AdressType    MainID    Location 

 1    ExampleCompany        H             0        Germany 

 2    ExampleCompany        N             1        Sweden
   
 3    ExampleCompany        N             1         Germany

and we got another Contacts Table including the latest Contact to each of the Company Locations

Contacts

ID    SuperID    Datecreate    Notes

1       1        10.04.2018     XY

2       3        09.04.2018     YX

3       2        11.04.2018      XX

Now we want to select the latest Contact per Company and sort them so we got a list of all our customers that we did not contact in a long time.

i thought about something like this:

SELECT 
ADDRH.ID,
ADDRH.COMPANY1,
TOPCONT.ID,
TOPCONT.DATECREATE,
TOPCONT.NOTES0

FROM dbo.ADDRESSES ADDRH
OUTER APPLY (SELECT TOP 1 ID, SUPERID, DATECREATE, CREATEDBY, NOTES0 FROM DBO.CONTACTS CONT WHERE ADDRH.ID = CONT.SUPERID ORDER BY DATECREATE DESC) TOPCONT
WHERE
TOPCONT.ID IS NOT NULL
ORDER BY TOPCONT.DATECREATE

But this is still missing the fact that we got the same company multiple times in the addresses table. how can i create a list that got each company with the latest contact?

Thanks for your help

Greetings

Well, you have to remove duplicates from address as well. Because of the structure of your data, I think the best approach is to use row_number():

SELECT ac.*
FROM (SELECT a.ID, a.COMPANY1, c.ID, c.DATECREATE, c.NOTES0,
             ROW_NUMBER() OVER (PARTITION BY a.COMPANY1 ORDER BY c.DATECREATE DESC) as seqnum
      FROM dbo.ADDRESSES a JOIN
           DBO.CONTACTS c
           ON a.ID = c.SUPERID
      WHERE c.ID IS NOT NULL
     ) ac
WHERE seqnum = 1
ORDER BY c.DATECREATE;