CODEWITHSUNDEEP
sqloracle-database
kanes
kanes

Reputation: 79

How to use only one query (no sub query) to get the rows which mach the requirement?

I need some suggestions for my case. First, I have a table (T1) like this:

         Date   ID  Flag 
   2020-08-05    a    0    
   2020-09-05    a    0
   2020-10-05    a    1
   2020-11-05    a    1 
   2020-12-05    a    0
   2020-08-05    b    1    
   2020-09-05    b    1
   2020-10-05    b    1
   2020-11-05    b    1 
   2020-12-05    b    1
   2020-08-05    c    0    
   2020-09-05    c    0
   2020-10-05    c    0
   2020-11-05    c    0 
   2020-12-05    c    0

I want to have a table like this:

         Date   ID  Flag  Result
   2020-08-05    a    0   (null)
   2020-09-05    a    0   (null)
   2020-10-05    a    1   (null)
   2020-11-05    a    1   (null)
   2020-12-05    a    0   (null)
   2020-08-05    b    1     T
   2020-09-05    b    1     T
   2020-10-05    b    1     T
   2020-11-05    b    1     T
   2020-12-05    b    1     T
   2020-08-05    c    0     F
   2020-09-05    c    0     F 
   2020-10-05    c    0     F 
   2020-11-05    c    0     F
   2020-12-05    c    0     F

The issue is, if I can use only one query (without any sub query), what I can do? I tried like this:

Select * case
when Flag = 0 then 'F'
when Flag = 1 then 'T' End as Result From T1;

But for the ID = a, if i used this query, the result is not right.
I know, i use 'With' or something else with a sub query (one moere 'select..'), the problem can be easily solved. So, what if i can use only one 'select...', any suggestions?

Upvotes: 0

Views: 51

Answers (2)

Mohan Reddy
Mohan Reddy

Reputation: 17

    create table flag_data(id varchar2(10),flag number(2));
       
    insert into flag_data values('a',0);
    insert into flag_data values('a',0);
    insert into flag_data values('a',1);
    insert into flag_data values('a',0);
    insert into flag_data values('a',1);
         
         
    insert into flag_data values('b',1);
    insert into flag_data values('b',1);
    insert into flag_data values('b',1);
    insert into flag_data values('b',1);
            
    insert into flag_data values('c',0);
    insert into flag_data values('c',0);
    insert into flag_data values('c',0);
    insert into flag_data values('c',0);
    insert into flag_data values('c',0);
    commit;
               
         
         select t.*,
           decode( min(flag) over (partition by id) - max(flag) over (partition by id),0
                 ,min(flag) over (partition by id) 
            ) as result
    from flag_data t;


select t.*,min(flag) over (partition by id) id1,max(flag) over (partition by id) id2,min(flag) over (partition by id) - max(flag) over (partition by id) minus_dta,
       decode( min(flag) over (partition by id) - max(flag) over (partition by id),0
             ,min(flag) over (partition by id) 
        ) as result
from flag_data t;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I think you want window functions to compare all the values for a given id. The following returns 0/1 (which makes sense to me:

select t.*,
       (case when min(flag) over (partition by id) = max(flag) over (partition by id)
             then min(flag) over (partition by id)
        end) as result
from t;

For Y/N:

select t.*,
       (case when min(flag) over (partition by id) = 1 and
                  max(flag) over (partition by id) = 1
             then 'Y'
             when min(flag) over (partition by id) = 0 and
                  max(flag) over (partition by id) = 0
             then 'N'                 
        end) as result
from t;

Note: This assumes that flag is never NULL, which is consistent with your data.

Upvotes: 3

Related Questions