CODEWITHSUNDEEP
sqlsql-serverrow-number
Joshua Cameron-Mackintosh
Joshua Cameron-Mackintosh

Reputation: 269

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

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:

SQL result

(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?

Upvotes: 1

Views: 1716

Answers (2)

Fuzzy
Fuzzy

Reputation: 3810

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;

Upvotes: 6

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

Distinct elaborate

Since another answer is achieving the result but in a "bad" manner, I'd go with one more derived table. If you apply ROW_NUMBER() function within the same level as DISTINCT clause, it wouldn't actually do that DISTINCT.

I thought it is worth pointing out, because if you remove the WHERE clause with row number limit, you'd end up having duplicate records in your output.

Example:

select distinct 
  tnid, 
  id, 
  row_number() over (partition by tnid order by id) as rn
from (
  select 1 as tnid, 1 as id 
  union all select 1,7 
  union all select 2,3 
  union all select 2,3 -- duplicate record
  union all select 2,9 
  union all select 1,5 
) foo

Result:

tnid | id | rn
-----+----+----
1    | 1  | 1
1    | 5  | 2
1    | 7  | 3
2    | 3  | 1
2    | 3  | 2 -- duplicate record showing in distinct, because it takes the rn column as well
2    | 9  | 3

Original answer

You need a ROW_NUMBER() window function that would enumerate rows for each TnID starting with lowest ID. Then just use a WHERE clause with a row_num = 1 to limit your output only to those rows:

SELECT [Tenant Name], ID, TnID, PrId
FROM (
    SELECT
        *
        , ROW_NUMBER() OVER (PARTITION BY TnID ORDER BY ID) AS row_num
    FROM (
        SELECT DISTINCT 
              [Tenant Name]
            , ctc.ID
            , cct.TnID
            , PrId
        FROM 
            CARL_Tenant_Contacts ctc
            JOIN CARL_Current_Tenants cct ON cct.ID = ctc.TnID
        WHERE 
            [Tenant Name] IS NOT NULL 
            AND [Tenant Name] != ''
        ) foo
    ) bar
WHERE row_num = 1

I took the time to assign aliases to your tables to make the query shorter.

Upvotes: 0

Related Questions