CODEWITHSUNDEEP
sqloracle-database
steve
steve

Reputation: 362

How to get Distinct value for a column on the basis of other column in Oracle

I want to get the distinct values from COL1 and it's COL3 value also but the condition is if COL1 = COl2 then it should pick the matching COL3 value otherwise pick the COL1 value if they are not same. I'm stuck in the logic, any help will be appreciated!

Please see the below image for more detail:

select  DISTINCT COL1, 
CASE WHEN COL1 = COL2 THEN COL3 END COL3 from TABLE1 
WHERE COL1 IS NOT NULL;

enter image description here

Upvotes: 0

Views: 1166

Answers (5)

forpas
forpas

Reputation: 164064

With a self join:

select distinct
  t.col1,
  case 
    when tt.col1 is null then t.col3  
    else tt.col3 
  end col3  
from tablename t left join tablename tt
on tt.col1 = t.col1 and tt.col2 = t.col1

See the demo.
Results:

> COL1 | COL3
> ---: | :---
>   11 | ABC 
>   12 | ABC 
>   13 | BDG 
>   14 | DEF 
>   15 | CEG

Upvotes: 0

Piotr Kamoda
Piotr Kamoda

Reputation: 1006

I think that you can join the table with itself and then use a join conditio to filter that out, then decide in select wether there was COL2 = COL1 and choose appropriate COL3:

SELECT DISTINCT a.COL1, CASE WHEN b.COL1 IS NULL THEN a.COL3 ELSE b.COL3 END as COL3
FROM TABLE1 a
LEFT JOIN TBALE2 b
on a.COL1 = b.COL2 
and a.COL1 = b.COL1

This way you have on table a all the data, and on table b data if and only if COL1 matches with COL2. Then you select whichever COL3 is not null, prefarably the one from table b. There is Oracle function coalesce that does just that.

Upvotes: 0

jarlh
jarlh

Reputation: 44696

Do a GROUP BY to get distinct COL1 values.

Use COALESCE() to return the COL3 value if there exists a COL1 = COL2 row, otherwise return the max COL3 value for the COL1. (Could use MIN() too, if that's better.)

select COL1,
       COALESCE( MAX(CASE WHEN COL1 = COL2 THEN COL3 END), MAX(COL3) )
FROM table1
WHERE COL1 IS NOT NULL
GROUP BY COL1

Upvotes: 1

Learner
Learner

Reputation: 1

select distinct COL1, if(COL1 = COL2, COL3, COL1) as result
from table1

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

use correlated subquery

select col1,col3 
from TABLE1 a 
      where col2 in (select min(col2) from table1 b where a.col1=b.col1)

Upvotes: 1

Related Questions