Filtering duplicates in a table without a primary key

Question

I would really appreciate your help with the following problem:

I have a table (stock_header) which has 3 columns of interest

catalogue_code  purcha  stock_
--------------  ------  ------
1001            Box     Box
1001            Box     Box
1002            EA      EA
1002            Set     Set
1002            Kit     Kit
1004            Set     Set

I would like to extract the information using the following rules:

• If a catalogue_code is a duplicate and all its purcha and stock_ attributes have the same value (e.g catalogue_code 1001), select any record.
• If a catalogue_code is a duplicate and some of its purcha and stock_ attributes contain different values (e.g catalogue_code 1002), select the record that has purcha = 'EA', stock_ = 'EA'.

I am having difficulties implementing this logic in T-SQL.

Update: Just wanted to mention that the original table doesn't have a primary key. The removal of duplicates is done so that the catalogue_code can be the primary key.

Update2: Unfortunately, answers by Nick and Elian do not solve the problem. Here is my pseudo code for a potential implementation:

1. Find all the duplicated catalogue_codes
2. For each duplicate do:
3. Get any pair of stock_ and purcha attributes. Compare the selected pair to all other pairs and store your results in a variable (numberOfIdenticalRecords).
4. If numberOfIdenticalRecords = 1 (meaning that all the other records have distinct stock_ and purcha values). In this case, select the record that has purcha = 'EA' and stock_ = 'EA'.
5. Else if numberOfIdenticalRecords > 1 (meaning that all the records contain the same stock_ and purcha values), select any record.

One big drawback of this implementation is that it is procedural whereas SQL is declarative. Even if it's possible to implement it, chances are that it's going to be hard to understand and maintain. Am I over thinking the whole thing? Is there a simpler way of doing it?

Answer 1

In the title you mention removing rows, but your question sounds like you want a select query. I must say that your requirements sound a bit strange, but I guess the following query will give you what you are looking for:

select 
    catalogue_code, purcha, stock_
from 
    stock_header
where 
    (purcha = 'EA' and stock_ = 'EA') 
    or catalogue_code not in(select catalogue_code from stock_header where purcha = 'EA' AND stock_ = 'EA')
group by 
    catalogue_code, purcha, stock_

If you really need to delete rows, then you need something to uniquely identify a row, like Erwin Brandstetter mentioned in his comment.

Answer 2

declare @T table
(
  catalogue_code int,
  purcha varchar(5),
  stock_ varchar(5)
)

insert into @T values
(1001,            'Box',     'Box'),
(1001,            'Box',     'Box'),
(1002,            'AA',      'AA'),
(1002,            'MM',      'MM'),
(1002,            'EA',      'EA'),
(1002,            'Set',     'Set'),
(1002,            'Kit',     'Kit'),
(1004,            'Set',     'Set')

;with C as
(
  select *,
         row_number() over(partition by catalogue_code 
                           order by case when purcha = 'EA'
                                         then 0
                                         else 1
                                    end) as rn
  from @T
)
select *
from C
where rn = 1

Result:

catalogue_code purcha stock_ rn
-------------- ------ ------ --------------------
1001           Box    Box    1
1002           EA     EA     1
1004           Set    Set    1

Try it on SE-Data Explorer: https://data.stackexchange.com/stackoverflow/q/114648/