How do I pull the first result from each unique result of an SQL query?

Question

I am trying to pull the first result from each unique TnID record, so that I can then pull this into another database.

SELECT DISTINCT [Tenant Name]
        ,CARL_Tenant_Contacts.ID
        ,Carl_current_tenants.TnID
        ,PrId FROM CARL_Tenant_Contacts 
        JOIN CARL_Current_Tenants ON CARL_Current_Tenants.ID = CARL_Tenant_Contacts.TnID
        WHERE [Tenant Name] IS NOT NULL 
        and [Tenant Name] != ''

This is the code I've gotten so far, however it doesn't do quite what I want.

Currently the result is:

(There are many more, this is just a small example.)

But I want something that will return the first name of each TnID, for example, from TnID1 I want Ms Julie Robinson, from TnID2 I want Ms Julia Gregg, TnID3 Mr Andrew Leigh webb and so on.

Is this feasible or am I attempting the impossible?

Answer 1

This should do it:

;WITH CTE
    AS (SELECT DISTINCT
             [Tenant Name]
            , CARL_Tenant_Contacts.ID
            , Carl_current_tenants.TnID
            , PrId
            , RN = ROW_NUMBER() OVER(PARTITION BY Carl_current_tenants.TnID ORDER BY CARL_Tenant_Contacts.ID)
        FROM   CARL_Tenant_Contacts
             JOIN CARL_Current_Tenants ON CARL_Current_Tenants.ID = CARL_Tenant_Contacts.TnID
        WHERE  [Tenant Name] IS NOT NULL
             AND [Tenant Name] != '')
    SELECT A.[Tenant Name]
        , A.ID
        , A.TnID
        , A.PrId
    FROM   CTE AS A 
    WHERE RN = 1;

Modification so that DISTINCT is handled:

;WITH A
    AS (SELECT DISTINCT
             [Tenant Name]
            , CARL_Tenant_Contacts.ID
            , Carl_current_tenants.TnID
            , PrId
        FROM   CARL_Tenant_Contacts
             JOIN CARL_Current_Tenants ON CARL_Current_Tenants.ID = CARL_Tenant_Contacts.TnID
        WHERE  [Tenant Name] IS NOT NULL
             AND [Tenant Name] != ''),
    CTE
    AS (SELECT A.[Tenant Name]
            , A.ID
            , A.TnID
            , A.PrId
            , RN = ROW_NUMBER() OVER(PARTITION BY A.TnID ORDER BY A.ID)
        FROM   A)
    SELECT A.[Tenant Name]
        , A.ID
        , A.TnID
        , A.PrId
    FROM   CTE AS A
    WHERE  RN = 1;