Mark Jeffries Mark Jeffries - 9 months ago 44
SQL Question

Find Duplicate Values in a column based on specific criteria

I have a table that holds actions against specific accounts, the actions are given a numbered SET of actions and within that SET they get a unique, sequential number. We ran into an issue where somehow one of the unique numbers had been duplicated and would like to check for more examples where this might have happened. The table looks a little like this:

Account | Action Set | Action No | Action Code
001 | 1 | 1 | GEN
001 | 1 | 2 | PHO
001 | 1 | 3 | RAN
002 | 1 | 1 | GEN
002 | 1 | 2 | PHO
002 | 1 | 3 | RAN

I have tried various things I've found through searches on here but can't find anything that looks like it fits my specific circumstances.

For any given account number, I would like to find where within one Action SET the same Action Number is used more than once. I also need to return the full row, not just a count of how many there are.

I would post what I have tried so far but honestly the extent of the code I have written so far is:




Answer Source

Based on your description, you can use exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.account = t.account and
                    t2.actionset = t.actionset and
                    t2.actionno <> t.actionno


The above assumes that action numbers are different. Otherwise you can use:

select t.*
from t
where (select count(*)
       from t t2
       where t2.account = t.account and
             t2.actionset = t.actionset 
      ) >= 2;