CODEWITHSUNDEEP
sqlsql-servert-sql
GrafixMastaMD
GrafixMastaMD

Reputation: 147

Eliminating Duplicate records based on priority field - TSQL

good morning. I do not think my head is thinking straight. I wrote the unions to get the following result set because I thought it might be easier this way to get the end value needed. Here nore there I can optimize this later. So I have the result set below. It's only in Excel so I could take a Screen Shot.

Table Schematic

Column A = EmployeeID

Column B = ActivityId

Column C = PriorityNumber

Column D = FSBillRate

Column E = Classification

Result Set

Anyways.. So if you norice (MD) Has a combo of A column and B Column twice .

MD Code:1

MD Code:1

But you notice that the first line has C column of 1 and the second line has C column of 3.

My Goal: To have only 1 combo of A and B Column based on column C. I want to keep the lower of the column C. So this case I would want to Keep 1. But in the case of BS code:1 4 and BS code:1 6 I would wan tto keep 4 obviously becasue 4 is lowwer than 6.

This must be accomplished in a SELECT statement as the data must remain and this is basically going to be a subquery to another query.

Upvotes: 1

Views: 1628

Answers (1)

SqlZim
SqlZim

Reputation: 38023

Try this: (Edited to update column names based on comment)

Original answer using Row_Number

with cte as (
  select [EmployeeID], [ActivityId], [Col3], [Col4], [Col5]
    , rownumber= row_number() over (
        partition by [EmployeeID], [ActivityId]
        order by [Col3]
        )
  from [table]

)
  select [EmployeeID], [ActivityId], [Col3], [Col4], [Col5]
    from cte
    where rownumber=1

Alternative solution using Cross Apply

select t.[EmployeeID], t.[ActivityId], x.[Col3], x.[Col4], x.[Col5]
  from [table] t
    cross apply (
      select top 1 i.[Col3], i.[Col4], i.[Col5] 
        from [table] i 
        where i.[EmployeeID] = t.[EmployeeID]
          and i.[ActivityId] = t.[ActivityId]
        order by x.[Col3]
          ) as x
  group by t.[EmployeeID], t.[ActivityId], x.[Col3], x.[Col4], x.[Col5]

Upvotes: 4

Related Questions