title: SQL Server - How to remove repeating 2nd column
body: <p>I have query that is similar to the following and wish to remove the repeating rows in the second/third column...need examples where I can use the following  (top 1, max, min, etc...) or other best method without using derived query to remove the duplicate values. Thanks JK</p>
  
  <pre><code>WITH cteEMPAssistants (executive_personnel_number, assistant_personnel_number, assistant_type, SecType) AS
    (SELECT DISTINCT CASE
                         WHEN ISNUMERIC(KA.KIT_asgn_emplid) = 1 THEN CAST(CAST(KA.KIT_asgn_emplid AS INT) AS VARCHAR(11))
                         ELSE ''
                     END AS executive_personnel_number ,
                     CAST(CAST(KAP.emplid AS INT) AS VARCHAR(11)) AS assistant_personnel_number ,
                     ISNULL(LATT1.xlatlongname, '') AS assistant_type ,
                     CAST(LATT1.fieldvalue AS VARCHAR(4)) AS SecType ,
                     ISNULL(LATT3.xlatshortname, '') AS assign_role
     FROM dbo.KIT_ASGN_PRNT AS KAP
     LEFT OUTER JOIN dbo.KIT_ASSIGNMENTS AS KA ON KA.emplid = KAP.emplid
     AND KA.effdt = KAP.effdt
     LEFT OUTER JOIN dbo.KIT_EMPLOYEES AS EXECT ON EXECT.EMPLID = KA.KIT_ASGN_EMPLID
     LEFT OUTER JOIN dbo.KIT_EMPLOYEES AS ASST ON ASST.EMPLID = KAP.EMPLID
     LEFT OUTER JOIN dbo.XLATITEM AS LATT1 ON LATT1.fieldname = 'KIT_ASGN_TYPE'
     AND LATT1.fieldvalue = KAP.KIT_asgn_type
     LEFT OUTER JOIN dbo.XLATITEM AS LATT3 ON LATT3.fieldname = 'KIT_ASGN_ROLE'
     AND LATT3.fieldvalue = KA.KIT_asgn_role
     AND LATT3.xlatshortname = 'Primary'
     WHERE KAP.effdt =
         (SELECT MAX(effdt)
          FROM dbo.KIT_ASGN_PRNT
          WHERE emplid = KAP.emplid
            AND effdt &lt;= GETDATE() ) --Return data only when employee and assistant  active; null is for temps
  
       AND (EXECT.EMP_STATUS = 'A'
            OR EXECT.EMP_STATUS IS NULL )
       AND ASST.EMP_STATUS = 'A' --Return all floating secretaries
  
       AND (KAP.KIT_asgn_type = 'F' --Return all assigned secretaries, who are assigned to a person and not a task
  
            OR (KAP.KIT_asgn_type IN ('A',
                                      'AF')
                AND KA.KIT_asgn_person = 'Y' ) ) )
  SELECT CAST(EMP.KIT_EMPLID AS VARCHAR(15)) AS employeeNumber ,
         KAP.emplid AS Assistant ,
         SECRES.SecType AS SecType
  FROM dbo.KIT_EMPLOYEES AS EMP
  LEFT OUTER JOIN cteEMPAssistants AS SECRES ON EMP.KIT_EMPLID = SECRES.assistant_personnel_number
  WHERE (EMP.EMP_STATUS = 'A')
    AND (EMP.PER_ORG IN ('EMP',
                         'CWR'))
    AND (ISNULL(EMP.KIT_NETWORK_ID, '') &lt;&gt; '')
  </code></pre>
  
  <p>Here is sample output:</p>
  
  <pre><code>employeeNumber    Assistant     SecType
  ---------------   ---------     -----------------
  1234               1112          A
  1234               1112          A
  1234               1112          A
  1234               1112          A
  4567               1113          NULL
  1278               1114          NULL
  1365               1115          NULL
  1298               1116          A
  1476               1117          A
  1191               1118          NULL
  </code></pre>