Forum Stats

  • 3,733,243 Users
  • 2,246,736 Discussions
  • 7,856,631 Comments

Discussions

How to Get ID Where each value of Flag Field is 1 for that ID

478924
478924 Member Posts: 2
edited December 2005 in SQL & PL/SQL
I have a table in the following form


ID Flag
1 1
1 1
1 0
2 1
2 1


I have to write a query that will return the ID Whose All Flags Values are 1. i.e if i run the query on the above table it must return 2.
If there is Flag for a specific ID equal to 0 it must return empty.

Please Help me.

Regards,

Ahmad Jalil Qarshi

Comments

  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    Hi,

    Try this :
    SQL> select * from tflag;

    ID FLAG
    ---------- ----------
    1 1
    1 1
    1 0
    2 1
    2 1

    SQL> select id
    2 from (select id, count(*) num_id, sum(flag) sum_flag from tflag group by id)
    3 where num_id=sum_flag
    4 /

    ID
    ----------
    2

    SQL>
    Nicolas.
  • 146850
    146850 Member Posts: 116
    edited December 2005
    Nicolas' query is not correct if flag has 3 or more values.
    So, more general method is this.
    SELECT   ID
        FROM tflag
    GROUP BY ID
      HAVING MIN (flag) = 1 AND MIN (flag) = MAX (flag)
    for more SQL problems & questions, please visit
    http://cafe.daum.net/oraclesqltuning
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    edited December 2005
    theaos,
    Nicolas' query is not correct if flag has 3 or more values.
    That's Ok, but the requirement of flag column sound to me like a flag with only two values : 0 or 1.

    Even if your sql plain is very nice, if you permit me to complete my first answer, I can modify my query with adding decode :
    SQL> select * from tflag;

    ID FLAG
    ---------- ----------
    1 1
    1 1
    1 0
    2 1
    2 1
    3 3
    3 0
    3 0

    8 rows selected.

    SQL> select id
    2 from (select id, count(*) num_id, sum(flag) sum_flag from tflag group by id)
    3 where num_id=sum_flag
    4 /

    ID
    ----------
    2
    3 <-- This is false, as theaos said

    SQL> ed
    Wrote file afiedt.buf

    1 select id
    2 from (select id,
    3 count(*) num_id,
    4 sum(decode(flag,1,1,0)) sum_flag from tflag group by id)
    5* where num_id=sum_flag
    SQL> /

    ID
    ----------
    2

    SQL>
    Nicolas.

    Anyway, the OP can keep the theaos' query...
    Message was edited by:
    N. Gasparotto
  • 146850
    146850 Member Posts: 116
    As you know, the last argument 0 of decode function is unnecessary in your modified query.
    Thanks.

    for more SQL problems & questions, please visit
    http://cafe.daum.net/oraclesqltuning
  • shishu
    shishu Member Posts: 223
    Please try to use this query.

    select id from temp
    group by id
    having min(flag)=max(flag)


    Thanks

    Shishu Paul
  • 478769
    478769 Member Posts: 86
    Please try to use this query.

    select id from temp
    group by id
    having min(flag)=max(flag)
    The above query will also select the id that has all the flag values as 0.
    So it can be modified as

    select id from temp
    group by id
    having min(flag)=max(flag)
    and max(flag)=1;
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    As you know, the last argument 0 of decode function is unnecessary in your modified query.
    Yes theaos ;-) but I think it's more readable with 0.

    Best regards,

    Nicolas.
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    SQL> select id from t having count(decode(flag,1,null,1))=0 group by id;
    ID
    ----------
    2

    will return all rows where all id whose all flags are 1
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    Please try to use this query.

    select id from temp
    group by id
    having min(flag)=max(flag)
    The above query will also select the id that has all
    the flag values as 0.
    So it can be modified as

    select id from temp
    group by id
    having min(flag)=max(flag)
    and max(flag)=1;
    well, min(flag)=1 and max(flag)=1 will not work for nulls, but maybe this column is not nullable..
  • William Robertson
    William Robertson Member Posts: 9,560 Bronze Crown
    edited December 2005
    This is fun:
    SELECT * FROM t t1
    WHERE  1 = ALL
           ( SELECT flag FROM t
             WHERE  id = t1.id );
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    very nice indeed !
  • John Spencer
    John Spencer Member Posts: 8,567
    Can I play too?
    SELECT * FROM t o
    WHERE NOT EXISTS (SELECT 1 FROM t i
    WHERE o.id = o.id and
    i.flag = 0);
    or, to accomodate more than two flag values:
    SELECT * FROM t o
    WHERE NOT EXISTS (SELECT 1 FROM t i
    WHERE o.id = t.id and
    DECODE(i.flag, 1, 1, 0) = 0);
    TIMTOWTDI
    John
  • 245482
    245482 Member Posts: 1,254
    Don't tell me that the good Mr. Spencer is a Perl afficionado. Oh the horror.
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    edited December 2005
    select id from t minus select id from t where lnnvl(flag=1);

    Message was edited by:
    Laurent Schneider
    of course the condition is flag=1 not id=1...
  • 474126
    474126 Member Posts: 914
    edited December 2005
    SELECT ID FROM (
    SELECT ID,COUNT(1) CNT FROM (SELECT ID,FLAG
    FROM SAMPLE
    GROUP BY ID,FLAG)
    GROUP BY ID)
    WHERE CNT=1
  • 245482
    245482 Member Posts: 1,254
    It seems that you just want to count the number of flags that are not 1. This way you only hit the table once -- which is nice if there's a bit more data.
    create table t (
      id  number,
      flag  number );
    
    insert into t values(1,1);         
    insert into t values(1,1);       
    insert into t values(1,0);       
    insert into t values(2,1);       
    insert into t values(2,1);       
    insert into t values(3,3);       
    insert into t values(3,0);       
    insert into t values(3,0);
    commit;
    
    SQL> SELECT   id
      2      FROM t
      3  GROUP BY id
      4    HAVING COUNT (DECODE (flag, 1, NULL, 'x')) = 0;
    
            ID
    ----------
             2
    
    SQL> 
  • 245482
    245482 Member Posts: 1,254
    'Course I'm just re-stating what Nicolas meant to say. Theaos' solution has trouble with NULLs.
    SQL> insert into t values(4,1);
    
    1 row created.
    
    SQL> insert into t values(4,NULL);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> 
    SQL> SELECT   ID
      2      FROM t
      3  GROUP BY ID
      4    HAVING MIN (flag) = 1 AND MIN (flag) = MAX (flag);
    
            ID
    ----------
             2
             4
    
    SQL> 
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    I have to say that the most funny query is the William's query, and work for null value.
    SQL> create table t (
    2 id number,
    3 flag number );

    Table created.

    SQL>
    SQL> insert into t values(1,1);

    1 row created.

    SQL> insert into t values(1,1);

    1 row created.

    SQL> insert into t values(1,0);

    1 row created.

    SQL> insert into t values(2,1);

    1 row created.

    SQL> insert into t values(2,1);

    1 row created.

    SQL> insert into t values(3,3);

    1 row created.

    SQL> insert into t values(3,0);

    1 row created.

    SQL> insert into t values(3,0);

    1 row created.

    SQL> insert into t values(4,1);

    1 row created.

    SQL> insert into t values(4,NULL);

    1 row created.

    SQL> SELECT distinct id FROM t t1
    WHERE 1 = ALL
    ( SELECT flag FROM t
    WHERE id = t1.id )
    SQL> /

    ID
    ----------
    2

    SQL>
    Nicolas.
  • 146850
    146850 Member Posts: 116
    edited December 2005
    Using group functions only,
    SELECT   ID
        FROM t
    GROUP BY ID
      HAVING SUM (flag) / COUNT (*) = 1 AND MAX (flag) = 1
    for more SQL problems & questions, please visit
    http://cafe.daum.net/oraclesqltuning
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    still 3 aggregations when one is enough. check examples above
This discussion has been closed.