CODEWITHSUNDEEP
sqloracle11g
Ruchita P
Ruchita P

Reputation: 389

with clause in oracle

I am trying to get below expected result with WITH clause in Oracle.

Sample Data
Id          C_Symbol    C_Id
1001        DD          201
1001        VR          202
1002        VR          203
1002        AS          205
1002        QA          206
1003        DD          207
1003        QA          208
1004        AS          209
1004        QA          210

Expected

Id          IS_DD   C_Id(min c_id for each id)
1001        Y       201
1002        N       203
1003        Y       207
1004        N       209

Meaning: If any id contains DD in c_symbol it should return flag Y with min c_id.

I tried to achieve this with WITH clause but it doesn't help. 

WITH A_Info As
    (Select id, c_symbol, c_id from XYZ),
    DD_Info As
    (select id, (case when c_symbol = 'DD' then 'Y' else 'N' END As MD), 
    row_number() over(partition by a_id order by c_id) as RN)
select * from DD_info where RN = 1;

This executes row wise, so looking for some alternate solution. Thank you,

Upvotes: 1

Views: 205

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I would just use aggregation:

select id,
       max(case when c_symbol = 'DD' then 'Y' else 'N' end) as is_dd,
       min(c_id) as c_id
from sample
group by id;

Here is an example.

Upvotes: 2

Related Questions