SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Help Needed to write select statement

479546Jun 27 2007 — edited Jul 9 2007

I have a table whose rows like this:

slno name testno
1 reni 1
2 arun 2
3 jithu 3
4 rajesh 4
5 shiju 1
6 Thomas 1
7 Thomas 2
8 Thomas 3
9 Thomas 4

many rows like above.I want to select names who have attended all the test that is testno 1,2,3,4

Looking forward to help from u

Nicolas Gasparotto

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

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

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

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

Please try to use this query.

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

Thanks

Shishu Paul

478769

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

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

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

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

This is fun:

SELECT * FROM t t1
WHERE  1 = ALL
       ( SELECT flag FROM t
         WHERE  id = t1.id );

Laurent Schneider

very nice indeed !

John Spencer

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

Don't tell me that the good Mr. Spencer is a Perl afficionado. Oh the horror.

Laurent Schneider

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

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

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

'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

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

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

still 3 aggregations when one is enough. check examples above

1 - 20

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 6 2007

Added on Jun 27 2007

23 comments

12,248 views

SQL & PL/SQL

Help Needed to write select statement

Comments

Post Details