Oracle query to select distinct Value based on another column value

Question

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.

What do you mean by " If value "123" is present ? with respect to what ? You said Id only has 123 or 456, so it should be present isn't it? — Kaushik Nayak
– Kaushik Nayak, Commented Mar 20, 2019 at 6:57

Gordon Linoff · Accepted Answer · 2019-03-20 11:54:25Z

1

simple group by will help you to get the desired result

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

edited Mar 20, 2019 at 11:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

answered Mar 20, 2019 at 6:35

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Viswa Over a year ago

Sorry forget to mention that ID is not a number. It is a string. So min(id) is not helpful.

Zaynul Abadin Tuhin Over a year ago

@Viswa cast it as a numeric use TO_NUMBER

Amir Kadyrov Over a year ago

min function can be applied not only for numbers.

psaraj12 Over a year ago

min function will not work if 123 is yyy and 456 is xxx

Gordon Linoff Over a year ago

@psaraj12 . . . Then use max(). It is only possible to answer the question that is asked, and this seems like this simplest answer.

|

Hong Van Vit · Accepted Answer · 2019-03-20 07:10:37Z

0

Some thing like this:

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

answered Mar 20, 2019 at 7:10

Hong Van Vit

3,0063 gold badges22 silver badges45 bronze badges

Comments

psaraj12 · Accepted Answer · 2019-03-20 10:25:58Z

0

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;

edited Mar 20, 2019 at 10:25

answered Mar 20, 2019 at 7:00

psaraj12

5,0783 gold badges25 silver badges34 bronze badges

Collectives™ on Stack Overflow

Oracle query to select distinct Value based on another column value

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related