CODEWITHSUNDEEP
sqloracle11g
Jatin
Jatin

Reputation: 3

Oracle SQL: Find duplicate CLIENTKEY and show one specific record

I have following table:

CLIENTKEY  CLIENTNAME             DEPARTMENT  HOSTKEY
0201967/6  PPBOP1BOP01-JO,BLOGS   KB          PPBOP1BOP01/MSC/PPBOP1BOP01/2/B1KI0
0201967/6  PPBOP1BOP01-JO,BLOGS   BS          PPBOP1BOP01/MSC/PPBOP1BOP01/2/B1KI0
0024028/2  PPBOP1BOP01-FOO,BAR    KB          PPBOP1BOP01/MSC/PPBOP1BOP01/2/B2KI0
0024028/2  PPBOP1BOP01-FOO,BAR    BS          PPBOP1BOP01/MSC/PPBOP1BOP01/2/B2KI0
1746947/1  BSM1BSM03-THING,BOB    BS          BSM1BSM03/BSHVS/BSM1BSM03/2/B1KI0
1612105/1  WIBU1IBU03-TREE,GREEN  BS          WIBU1IBU03/SHVS/WIBU1IBU03/3/B1KI0

The output I'm trying to get:

CLIENTKEY  CLIENTNAME             DEPARTMENT  HOSTKEY
0201967/6  PPBOP1BOP01-JO,BLOGS   KB          PPBOP1BOP01/MSC/PPBOP1BOP01/2/B1KI0
0024028/2  PPBOP1BOP01-FOO,BAR    KB          PPBOP1BOP01/MSC/PPBOP1BOP01/2/B2KI0
1746947/1  BSM1BSM03-THING,BOB    BS          BSM1BSM03/BSHVS/BSM1BSM03/2/B1KI0
1612105/1  WIBU1IBU03-TREE,GREEN  BS           WIBU1IBU03/SHVS/WIBU1IBU03/3/B1KI0

So need to show where DEPARTMENT = 'KB' when CLIENTKEY is duplicate but where there is no duplicate CLIENTKEY bring them all back.

Can this be achieved in a SQL statement?

Upvotes: 0

Views: 61

Answers (1)

Alex Poole
Alex Poole

Reputation: 191435

You could use a subquery which assigns a ranking to each row, so that where there is a duplicate key (one KB, one anything else) the KB row is ranked higher; and then filter on that:

-- CTE for sample data
with your_table (clientkey, clientname, department, hostkey) as (
  select '0201967/6', 'PPBOP1BOP01-JO,BLOGS', 'KB', 'PPBOP1BOP01/MSC/PPBOP1BOP01/2/B1KI0' from dual
  union all
  select '0201967/6', 'PPBOP1BOP01-JO,BLOGS', 'BS', 'PPBOP1BOP01/MSC/PPBOP1BOP01/2/B1KI0' from dual
  union all
  select '0024028/2', 'PPBOP1BOP01-FOO,BAR', 'KB', 'PPBOP1BOP01/MSC/PPBOP1BOP01/2/B2KI0' from dual
  union all
  select '0024028/2', 'PPBOP1BOP01-FOO,BAR', 'BS', 'PPBOP1BOP01/MSC/PPBOP1BOP01/2/B2KI0' from dual
  union all
  select '1746947/1', 'BSM1BSM03-THING,BOB', 'BS', 'BSM1BSM03/BSHVS/BSM1BSM03/2/B1KI0' from dual
  union all
  select '1612105/1', 'WIBU1IBU03-TREE,GREEN', 'BS', 'WIBU1IBU03/SHVS/WIBU1IBU03/3/B1KI0' from dual
)
-- actual query
select clientkey, clientname, department, hostkey
from (
  select clientkey, clientname, department, hostkey,
    rank () over (partition by clientkey
      order by case when department = 'KB' then 0 else 1 end) as rnk
  from your_table
)
where rnk = 1;

CLIENTKEY CLIENTNAME            DE HOSTKEY                            
--------- --------------------- -- -----------------------------------
0024028/2 PPBOP1BOP01-FOO,BAR   KB PPBOP1BOP01/MSC/PPBOP1BOP01/2/B2KI0
0201967/6 PPBOP1BOP01-JO,BLOGS  KB PPBOP1BOP01/MSC/PPBOP1BOP01/2/B1KI0
1612105/1 WIBU1IBU03-TREE,GREEN BS WIBU1IBU03/SHVS/WIBU1IBU03/3/B1KI0 
1746947/1 BSM1BSM03-THING,BOB   BS BSM1BSM03/BSHVS/BSM1BSM03/2/B1KI0

This will still allow duplicates in other departments, if that can happen, and will include all those rows; it will only exclude duplicates for KB.

Upvotes: 4

Related Questions