Forum Stats

  • 3,826,110 Users
  • 2,260,598 Discussions
  • 7,896,789 Comments

Discussions

duplicate record requirement

User_AFYOO
User_AFYOO Member Posts: 13 Red Ribbon
edited Mar 15, 2022 6:07AM in SQL & PL/SQL

Oracle 9i

I have data in table a, which is my source of truth, which states that each account should have just one plan..

 with table_a AS (
 ( SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual   
   union all
  SELECT 4722 AS account_no, 'BYO Plan' plan FROM dual
   )
  
 ), table_b  AS (
  ( SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual
   UNION ALL
   SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual
   UNION ALL
   SELECT 4722 AS account_no, 'Mobile Medium Small' plan FROM dual
   UNION ALL
   SELECT 4722 AS account_no, 'BYO Plan' plan FROM dual
  ) 

I'm looking for a result that filters out duplicate records in table b. For instance, in account 7056 table b, a row looked to be repeated in table a, thus I'd want to maintain one of those rows.

And because the account 4722 row with 'Mobile Medium Small' table b is not in table a, I'd want to preserve it.

This my expected output. Thank you


Tagged:

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    Answer ✓

    This is how I'd do it...

    with table_a AS (
      SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual union all
      SELECT 4722 AS account_no, 'BYO Plan' plan FROM dual
     )
     ,table_b AS (
      SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual UNION ALL
      SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual UNION ALL
      SELECT 4722 AS account_no, 'Mobile Medium Small' plan FROM dual UNION ALL
      SELECT 4722 AS account_no, 'BYO Plan' plan FROM dual
     )
     ,dup_remove as (
      select account_no, plan, row_number() over (partition by account_no, plan order by 1) as rn
      from   table_b
      minus 
      select account_no, plan, row_number() over (partition by account_no, plan order by 1) as rn
      from table_a
     )
    select account_no, plan
    from   dup_remove
    /
    
    ACCOUNT_NO PLAN
    ---------- -------------------
          4722 Mobile Medium Small
          7056 Mobile Plan Small
    
    


    I can't recall if the row_number analytic function was available back on Oracle 9i. It's about time you upgraded to a supported version as your version is about 2 decades out of date and totally unsupported.

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    Answer ✓

    This is how I'd do it...

    with table_a AS (
      SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual union all
      SELECT 4722 AS account_no, 'BYO Plan' plan FROM dual
     )
     ,table_b AS (
      SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual UNION ALL
      SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual UNION ALL
      SELECT 4722 AS account_no, 'Mobile Medium Small' plan FROM dual UNION ALL
      SELECT 4722 AS account_no, 'BYO Plan' plan FROM dual
     )
     ,dup_remove as (
      select account_no, plan, row_number() over (partition by account_no, plan order by 1) as rn
      from   table_b
      minus 
      select account_no, plan, row_number() over (partition by account_no, plan order by 1) as rn
      from table_a
     )
    select account_no, plan
    from   dup_remove
    /
    
    ACCOUNT_NO PLAN
    ---------- -------------------
          4722 Mobile Medium Small
          7056 Mobile Plan Small
    
    


    I can't recall if the row_number analytic function was available back on Oracle 9i. It's about time you upgraded to a supported version as your version is about 2 decades out of date and totally unsupported.

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    I can't recall if the row_number analytic function was available back on Oracle 9i.

    Since 8i.

    with table_a AS (
      SELECT 123 AS account_no, 'abc' plan FROM dual union all
      SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual union all
      SELECT 4722 AS account_no, 'BYO Plan' plan FROM dual
     )
     ,table_b AS (
      SELECT 123 AS account_no, 'abc' plan FROM dual union all
      SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual UNION ALL
      SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual UNION ALL
      SELECT 4722 AS account_no, 'Mobile Medium Small' plan FROM dual UNION ALL
      SELECT 4722 AS account_no, 'BYO Plan' plan FROM dual union all
      SELECT 4722 AS account_no, 'Mobile Medium Small' plan FROM dual UNION ALL
      SELECT 4722 AS account_no, 'BYO Plan' plan FROM dual
     )
     ,dup_remove as (
      select account_no, plan, row_number() over (partition by account_no, plan order by 1) as rn
      from   table_b
      minus 
      select account_no, plan, row_number() over (partition by account_no, plan order by 1) as rn
      from table_a
     )
    select account_no, plan
    from   dup_remove
    /
    ACCOUNT_NO PLAN               
    ---------- -------------------
          4722 BYO Plan           
          4722 Mobile Medium Small
          4722 Mobile Medium Small
          7056 Mobile Plan Small  
    


  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    Since 8i.

    I didn't have a version lying around to try it on... not many people do nowadays... I guess you're an exception. 😄

  • mark123
    mark123 Member Posts: 80 Blue Ribbon

    Is it me or does the 8i example above prove that the accepted solution doesn't always work ?

    ACCOUNT_NO PLAN               
    ---------- -------------------
          4722 BYO Plan           
          4722 Mobile Medium Small
          4722 Mobile Medium Small
          7056 Mobile Plan Small 
    


  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    edited Mar 17, 2022 9:46AM


    In what way?

    The requirement appears to be about removing each instance from table A that exists in table B, without removing ALL of them because there are duplicates in Table B.

    So in that example, account 123 with plan "abc" is removed. One instance of account 7056 with plan "Mobile Plan Small" is removed, leaving the other instance, and One instance of account 4722 with plan "BYO Plan" is removed, leaving the other instance of that and all other 4722 records.

    I guess it depends if the OP just wants to retain only 1 of the rows that match with table A or they only want to remove each instance given in table A? Which is certainly not clear from the original question. If they actually want to remove all but one instance of anything given in table A then that would certainly be a different matter. In fact it would appear to be a bit more of "remove the 1 instance if there is only 1, but remove all but one instance if there is more than 1"... which would seem a very odd requirement.

    Up to the OP to tell us I guess.

    Edit: I guess a simple solution if just 1 of each is required at the end it so just add a DISTINCT to the final query. ;)