CODEWITHSUNDEEP
sqloracle-database
Viswa
Viswa

Reputation: 11

Oracle query to select distinct Value based on another column value

Consider the below sample table:

ID    Value
123   ABC
456   DEF
456   ABC

I want the select query result as below:

ID    Value
123   ABC
456   DEF

Note: ID has only 2 different values - "123" and "456". Selection should be based on Column "ID". If value "123" is present, the corresponding data from "Value" column has to be selected. If not "456" ID should be retrieved.

Upvotes: 1

Views: 423

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32011

simple group by will help you to get the desired result

select min(id), value
from table
group by value

Upvotes: 1

psaraj12
psaraj12

Reputation: 5072

You can use the below if ID is a string like 456 is 'xxx' and 123 is 'yyy' The SQL fiddle here

WITH tt1 
     AS (SELECT Decode(id, '123', 1, 
                           2) order1, 
                id, 
                value1 
         FROM   tt), 
     tt2 
     AS (SELECT Min(order1) order1, 
                value1 
         FROM   tt1 
         GROUP  BY value1) 
SELECT tt1.id, 
       tt1.value1 
FROM   tt2, 
       tt1 
WHERE  tt1.value1 = tt2.value1 
       AND tt1.order1 = tt2.order1;

Upvotes: 0

Hong Van Vit
Hong Van Vit

Reputation: 2986

Some thing like this:

    select min(id) as id,
           Value 
    from table
    group by  Value, id

Upvotes: 0

Related Questions