CODEWITHSUNDEEP
oracle-databasedistinct
flyingbird013
flyingbird013

Reputation: 476

Oracle : how to remove row without distinct

i have two tables in oracle --> master_file and payment tables.

master_file table:

id (primary key), nama, status
41121, john, PL
41122, ryan, UP
41121, john, UP

there are duplicate data in columns id ( id 41121 ) . I do not know where the mistake , so the id column can store duplicate data .

payment table:

id, idr
41121, 1000
41122, 500

my query :

select a.id,a.nama,a.status,b.idr from master_file a, payment b
where a.id=b.id(+)

result

id, nama, status, idr
41121, john, PL, 1000
41122, ryan, UP, 500
41121, john, UP, 1000

i want result 

id, nama, status, idr
41121, john, PL, 1000
41122, ryan, UP, 500

i know, i cannot use distinct to remove row : 41121, john, UP, 1000.

give me solution, please. thanks before. :)

Upvotes: 0

Views: 69

Answers (2)

Alex Poole
Alex Poole

Reputation: 191435

Your master table cannot have a primary key on ID as it has duplicate values; perhaps it has a foreign key or a composite primary key, say on ID and status.

Assuming your data and data model are correct and you don't want to modify it, you need to establish the precedence of the possible status values. From your question it seems PL takes precedence over UP, but there may be other possible values, and you may have more than two rows for a given ID. Once you know the precedence you can use that in a case statement to create a numeric equivalent and use that to rack the 'duplicate' rows:

select a.id, a.nama, a.status, b.idr,
  dense_rank() over (partition by a.id order by 
    case a.status when 'UP' then 1 when 'PL' then 2 else 0 end desc) as rnk
from master_file a
left join payment b on b.id = a.id
order by a.id;

        ID NAMA       STATUS        IDR        RNK
---------- ---------- ------ ---------- ----------
     41121 john       PL           1000          1 
     41121 john       UP           1000          2 
     41122 ryan       UP            500          1

You can then use that as an inline view and filter out all but the highest-precedence rows:

select id, nama, status, idr
from (
  select a.id, a.nama, a.status, b.idr,
    dense_rank() over (partition by a.id order by 
      case a.status when 'UP' then 1 when 'PL' then 2 else 0 end desc) as rnk
  from master_file a
  left join payment b on b.id = a.id
)
where rnk = 1
order by id;

        ID NAMA       STATUS        IDR
---------- ---------- ------ ----------
     41121 john       PL           1000 
     41122 ryan       UP            500

It's also possible to use an inline view that filter the master_file before joining, but that's largely a matter of preference.

SQL Fiddle demo.

If the possible status values are in another reference table, and their precedence is established in that table, then you could join to that instead of doing it manually through case.

Upvotes: 1

robert
robert

Reputation: 4867

You want to remove the row with 'UP' in it?

In that case you need to include it as another item in your WHERE clause:

... where a.id=b.id(+) and status = 'UP'

Upvotes: 0

Related Questions