CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
SBB
SBB

Reputation: 8970

TSQL Group By Query

I have a query that is getting A.QID from a table where it could be contained more than one time. It will typically occur 12 times as there is a data import once per month.

I need to only query their data one time so I need to do DISTINCT on the A.QID field but I am having trouble doing so.

    SELECT   A.QID,
             (SELECT   TOP 1 E.[FirstName], -- Now we need to figure out who this person is by checking the historical table
                             E.[LastName],
                             E.[NTID],
                             E.[TitleDesc]
              FROM     employeeTable_historical AS E
              WHERE    E.qid = A.[QID]
                       AND CONVERT (DATE, A.[timestamp]) > CONVERT (DATE, E.[Meta_LogDate])
              ORDER BY meta_logDate DESC
              FOR      XML PATH (''), TYPE, ELEMENTS)
    FROM     [red].[dbo].[attritionCounts] AS A
    WHERE    A.[mgrQID] = @director -- Search all people managers under this director
             AND YEAR(CAST (A.[timestamp] AS DATE)) = @year
    ORDER BY A.lvl

I Tried SELECT DISTINCT(A.QID) ... but that didnt work either, got this error The xml data type cannot be selected as DISTINCT because it is not comparable.

Upvotes: 0

Views: 67

Answers (1)

Steve
Steve

Reputation: 5545

A simple solution is to limit your list to distinct before you add the XML to it:

SELECT   A.QID,
         (SELECT   TOP 1 E.[FirstName], -- Now we need to figure out who this person is by checking the historical table
                         E.[LastName],
                         E.[NTID],
                         E.[TitleDesc]
          FROM     employeeTable_historical AS E
          WHERE    E.qid = A.[QID]
                   AND CONVERT (DATE, A.[timestamp]) > CONVERT (DATE, E.[Meta_LogDate])
          ORDER BY meta_logDate DESC
          FOR      XML PATH (''), TYPE, ELEMENTS)
FROM     
         --This is the only part I changed so it is not the full table but the distinct of the 2 columns you need
         ( 
         SELECT DISTINCT QID, [timestamp]
         FROM [red].[dbo].[attritionCounts] 
         WHERE [mgrQID] = @director -- Search all people managers under this director
         AND YEAR(CAST ([timestamp] AS DATE)) = @year
         ) AS A
    ORDER BY A.lvl

EDIT: If timestamp is not unique to the QID, you can use the ROW_NUMBER() to get just the first one:

SELECT   A.QID,
         (SELECT   TOP 1 E.[FirstName], -- Now we need to figure out who this person is by checking the historical table
                         E.[LastName],
                         E.[NTID],
                         E.[TitleDesc]
          FROM     employeeTable_historical AS E
          WHERE    E.qid = A.[QID]
                   AND CONVERT (DATE, A.[timestamp]) > CONVERT (DATE, E.[Meta_LogDate])
          ORDER BY meta_logDate DESC
          FOR      XML PATH (''), TYPE, ELEMENTS)
FROM     
         --This is the only part I changed so it is not the full table but the distinct of the 2 columns you need
         ( 
         SELECT QID, [timestamp]
         FROM 
            (
            SELECT QID, [timestamp],
              ROW_NUMBER() OVER(PARTITION BY QID ORDER BY [Timestamp]) Row
            FROM [red].[dbo].[attritionCounts] 
            WHERE [mgrQID] = @director -- Search all people managers under this director
              AND YEAR(CAST ([timestamp] AS DATE)) = @year
            ) Tmp1
         WHERE Row = 1
         ) AS A
    ORDER BY A.lvl

Upvotes: 1

Related Questions