CODEWITHSUNDEEP
sqldb2
Mrblue1123
Mrblue1123

Reputation: 5

In SQL query to find duplicates in one column then use a second column to determine which record to return

So I have a table where data is like this:

╔═══════════╦═════════════╦═════════╗
║   Column1 ║   Column2   ║ Column3 ║
╠═══════════╬═════════════╬═════════╣
║ 127001126 ║ 90489495251 ║       1 ║
║ 127001126 ║ 90489495251 ║       2 ║
║ 134024323 ║ 81999000004 ║       1 ║
║ 346122930 ║   346000016 ║       2 ║
║ 346122930 ║   346000016 ║       1 ║
║ 346122930 ║   346000016 ║       3 ║
║ 346207637 ║   346000016 ║       1 ║
║ 346207637 ║   346000016 ║       3 ║
╚═══════════╩═════════════╩═════════╝

I would need an output:

╔═══════════╦═════════════╦═════════╗
║  Column1  ║   Column2   ║ Column3 ║
╠═══════════╬═════════════╬═════════╣
║ 127001140 ║ 90489495251 ║ 2       ║
║ 134024323 ║ 81999000004 ║ NULL    ║
║ 346122930 ║   346000016 ║ 2       ║
║ 346207637 ║   346000016 ║ NULL    ║
╚═══════════╩═════════════╩═════════╝

Essentially check if there are multiple records where column 1&2 check to see if there is a record with column3 = 2 if there is pull that record back. If column 1&2 never have a combination where column3 = 2 then pull one record back with column3 = null

EDIT: Code I tried:

   Select *
from (SELECT* From sometable
Where   column3 = 2) D Full outer join 
(SELECT*  FROM sometable
Where   coulmn3 = 1) E 
on  D.Column1 = E.Column1 
and D.Column2 = E.Column2
(SELECT * from
FROM    sometable
    Where   coulmn3 = 3) F Full outer join
    on  E.Column1 = F.Column1 
    and E.Column2 = F.Column2

I know the above will not change the column3 value to null. But i though=t it would get me to have at least only one record for each column1 and column2 combo

Upvotes: 0

Views: 1286

Answers (1)

Trinimon
Trinimon

Reputation: 13957

I think that should do the job on a DB2 as well:

SELECT Column1, Column2, 
       MAX (CASE Column3 WHEN 2 THEN 2 ELSE NULL END)
  FROM t
 GROUP BY Column1, Column2;

See this Fiddle for an ORACLE database.

Result:

COLUMN1     COLUMN2         COLUMN3
---------   -----------     -------
134024323   81999000004     (null)
127001126   90489495251     2
346122930   346000016       2
346207637   346000016       (null)

Upvotes: 1

Related Questions