CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloper
merts97
merts97

Reputation: 47

Oracle how to return rows based on condition?

I am trying to return id and name based on flag column. If id has a rows with flag = 1 my query should only return these rows. If it hasn't flag=1 value it should return rows with flag = 0. What is the best way for it ? Here is sample data :

id name flag

5  aa    1
5  bb    0
6  cc    1
10 dd    0
11 ee    1
11 ee    0

Expected output is :

id name flag

5  aa    1
6  cc    1
10 dd    0
11 ee    1

Upvotes: 0

Views: 333

Answers (2)

Mahamoutou
Mahamoutou

Reputation: 1555

You can use the keep dense_rank aggregating function to acheive that like below.

with t (id, name, flag) as (
select 5 , 'aa', 1 from dual union all
select 5 , 'bb', 0 from dual union all
select 6 , 'cc', 1 from dual union all
select 10, 'dd', 0 from dual union all
select 11, 'ee', 1 from dual union all
select 11, 'ee', 0 from dual
)
select id
    , max(name)keep(dense_rank last order by id, flag) name
    , max(flag)keep(dense_rank last order by id, flag) flag
from t
where flag in (0, 1)
group by id
order by id
;

Upvotes: 0

Tomáš Záluský
Tomáš Záluský

Reputation: 12000

Assuming flag column contains only 0 or 1, select rows whose flag is equal to maximal value of flags of given id:

select id, name, flag
from (
  select id, name, flag, max(flag) over (partition by id) as m
  from your_table
) x
where x.flag = x.m

Upvotes: 1

Related Questions