Skip to Main Content
Forums

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!

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

Comments

NicloeiW

a simple solution

QL> 
SQL> With t As
  2  (
  3    Select 1 s, 'reni'Name , 1 test From dual Union All
  4    Select 2, 'arun'  , 2           From dual Union All
  5    Select 3, 'jithu' , 3           From dual Union All
  6    Select 4, 'rajesh', 4           From dual Union All
  7    Select 5, 'shiju' , 1           From dual Union All
  8    Select 6, 'Thomas', 1           From dual Union All
  9    Select 7, 'Thomas', 2           From dual Union All
 10    Select 8, 'Thomas', 3           From dual Union All
 11    Select 9, 'Thomas', 4           From dual
 12  )
 13  Select a.Name,
 14         a.s,
 15         a.test
 16  From
 17  ( Select *
 18    From t
 19  ) a,
 20   (
 21    Select Name,
 22           Count(Name) cnt
 23    From t
 24    Group By Name
 25    Having Count(Name) = 4
 26   ) b
 27  Where a.Name = b.Name
 28  /

NAME            S       TEST
------ ---------- ----------
Thomas          6          1
Thomas          7          2
Thomas          8          3
Thomas          9          4

SQL>
500237
Hi,

You can try something like this

SELECT count(*),name from TEST group by name having count(test)>3;

Rgds,
Aashish S.
479546
Thanks Nicloei ofr ur help

I have more than 1000 rows like that and many more to come.So that query will not be feasible i think.Thank u once agin
479546
Thanks Aashish

But the test no is not in order in exact case
NicloeiW
so use that query and modify your query for 100, 1000 or 10000 rows,,,,
479546
Thanks

You mean to write the 1000 names like tht
526547

What do you mean?

I think Aashish solution is correct. A slight variation if "names can retake tests"

select name, count (distinct testno)
from   <yourtable>
group  by name
having count( distinct testno) = <no. of tests>

In your example, no. of tests will be 4.

NicloeiW
Thanks

You mean to write the 1000 names like tht

omg ??? this is silly question ??

use table name, in my query instead of t,
and use column name from that table

or i should tell you that also ??

Message was edited by:
Nicloei W
ushitaki 
with t as
(
  select 1 s, 'reni' name , 1 test from dual union all
  select 2,   'arun'      , 2      from dual union all
  select 3,   'jithu'     , 3      from dual union all
  select 4,   'rajesh'    , 4      from dual union all
  select 5,   'shiju'     , 1      from dual union all
  select 6,   'Thomas'    , 1      from dual union all
  select 7,   'Thomas'    , 2      from dual union all
  select 8,   'Thomas'    , 3      from dual union all
  select 9,   'Thomas'    , 4      from dual)
select a.name
      ,a.s
      ,a.test
from t a
where exists  
  ( select 't'  
      from t b
    where b.test in (1,2,3,4)
      and b.name = a.name
    having count(distinct b.test) = 4
  )
;
nurhidayat

a more generic solution...

with t as  (
  select 1 slno, 'reni' name, 1 testno from dual union all
  select 2, 'arun', 2 from dual union all
  select 3, 'jithu', 3 from dual union all
  select 4, 'rajesh', 4 from dual union all
  select 5, 'shiju', 1 from dual union all
  select 6, 'Thomas', 1 from dual union all
  select 7, 'Thomas', 2 from dual union all
  select 8, 'Thomas', 3 from dual union all
  select 9, 'Thomas', 4 from dual 
)
select *
from (
  select t1.*, sys_connect_by_path(testno,'/') path
  from ( select t.*, lag(slno) over(partition by name order by testno) slno_lag from t ) t1
  start with slno_lag is null connect by slno_lag = prior slno
)
where path like '%/' || to_char(1) || '/' || to_char(2) ||
                '/' || to_char(3) || '/' || to_char(4)

just modify the where clause to match your criteria.
note: not tested for performance.

474126
Many ppl have given you solutions and suggestions. Did you try those?

Anyway, check whether this helps or not?
SQL> create table t as
  2  select 1 slno,'reni' name, 1 testno from dual union all
  3  select 2,'arun',2  from dual union all
  4  select 3,'jithu',3 from dual union all
  5  select 4,'rajesh',4 from dual union all
  6  select 5,'shiju',1 from dual union all
  7  select 6,'Thomas',1 from dual union all
  8  select 7,'Thomas',2 from dual union all
  9  select 8,'Thomas',3 from dual union all
 10  select 9,'Thomas',4 from dual ;

Table created.

SQL> select * from t
  2  where name in
  3          (select name from t
  4          group by name
  5          having sum(decode(testno,1,1,2,1,3,1,4,1,0))=4);

      SLNO NAME       TESTNO
---------- ------ ----------
         6 Thomas          1
         7 Thomas          2
         8 Thomas          3
         9 Thomas          4

SQL> select slno,name,testno
  2  from
  3  (select slno,name,testno,count(1) over(partition by name) cnt from t)
  4  where cnt =4;

      SLNO NAME       TESTNO
---------- ------ ----------
         9 Thomas          4
         6 Thomas          1
         7 Thomas          2
         8 Thomas          3

SQL>
474126
When you try dont include the WITH clause. Since we dont have ur table and data, we showed an example with subset of data. Also change the "t" with yout table name and try. Solutions should work provided you try the proper query
NicloeiW
well i guess that to much of spoon feeding ;-)

i am not sure if he is trying that or not ?
474126
Nothing wrong in helping. Seems he is desperate for the help.
Does helping going to harm us? :-)
NicloeiW
no..
everyone needs help , it doesnt harm us, but if he cant use the query with as given to him, then i guess he should put an effort, like this he can learn better, 4 solutions are given to him,

that would benefit him in long run
Avinash Tripathi

Hi Mohan,

I think these solution won't work if any person has attampted more than once for a single test.

SQL> select * from t;

      SLNO NAME       TESTNO
---------- ------ ----------
         1 reni            1
         2 arun            2
         3 jithu           3
         4 rajesh          1
         5 shiju           1
 6 Thomas 1
7 Thomas 2
8 Thomas 3
9 Thomas 1

9 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  select slno,name,testno
  2  from
  3   (select slno,name,testno,count(1) over(partition by name) cnt from t)
  4* where cnt =4
SQL> /

    SLNO NAME TESTNO
---------- ------ ----------
9 Thomas 1
6 Thomas 1
7 Thomas 2
8 Thomas 3
SQL>


So the modified version of your query would be:

SQL> ed
Wrote file afiedt.buf

  1  SELECT slno,name,testno FROM (
  2  SELECT slno,name,testno, count(1) OVER (PARTITION BY NAME ORDER BY NAME) cnt
  3  FROM (
  4  select slno,name,testno
  5  from
  6   (select slno,name,testno,
  7    ROW_NUMBER() OVER(PARTITION BY NAME, TESTNO ORDER BY NAME) RN
  8   from t)
  9  where RN =1
 10* )) WHERE cnt=4
SQL> /

no rows selected

SQL>
SQL> ed
Wrote file afiedt.buf

  1  SELECT slno,name,testno FROM (
  2      SELECT slno,name,testno, count(1) OVER (PARTITION BY NAME ORDER BY NAME) cnt
  3      FROM (
  4      select slno,name,testno
  5      from
  6       (select slno,name,testno,
  7        ROW_NUMBER() OVER(PARTITION BY NAME, TESTNO ORDER BY NAME) RN
  8      from t)
  9      where RN =1
 10* )) WHERE cnt=4
SQL> /

      SLNO NAME       TESTNO
---------- ------ ----------
         6 Thomas          1
         7 Thomas          2
         8 Thomas          3
         9 Thomas          4

SQL> select * from t;

      SLNO NAME       TESTNO
---------- ------ ----------
         1 reni            1
         2 arun            2
         3 jithu           3
         4 rajesh          1
         5 shiju           1
         6 Thomas          1
         7 Thomas          2
         8 Thomas          3
         9 Thomas          4

9 rows selected.

[PRE]


Regards
ushitaki
If that was not unique on combining name and testno, then it gets wrong results.
But, you have goot point.
SQL> with t as(
  2  select 1 slno,'reni' name, 1 testno from dual union all
  3  select 2,'arun',2  from dual union all
  4  select 3,'jithu',3 from dual union all
  5  select 4,'rajesh',4 from dual union all
  6  select 5,'shiju',1 from dual union all
  7  select 6,'Thomas',1 from dual union all
  8  select 7,'Thomas',2 from dual union all
  9  select 8,'Thomas',3 from dual union all
 10  select 9,'Thomas',4 from dual union all
 11  select 10,'Thomas Jr.',1 from dual union all
 12  select 11,'Thomas Jr.',1 from dual union all
 13  select 12,'Thomas Jr.',2 from dual union all
 14  select 13,'Thomas Jr.',2 from dual union all
 15  select 20,'Thomas 3rd',1 from dual union all
 16  select 21,'Thomas 3rd',2 from dual union all
 17  select 22,'Thomas 3rd',3 from dual union all
 18  select 23,'Thomas 3rd',4 from dual union all
 19  select 24,'Thomas 3rd',5 from dual
 20  )
 21  select * from t
 22  where name in
 23          (select name from t
 24          group by name
 25          having sum(decode(testno,1,1,2,1,3,1,4,1,0))=4)
 26  ;

      SLNO NAME           TESTNO
---------- ---------- ----------
         9 Thomas              4
         8 Thomas              3
         7 Thomas              2
         6 Thomas              1
        13 Thomas Jr.          2
        12 Thomas Jr.          2
        11 Thomas Jr.          1
        10 Thomas Jr.          1
        24 Thomas 3rd          5
        23 Thomas 3rd          4
        22 Thomas 3rd          3
        21 Thomas 3rd          2
        20 Thomas 3rd          1

13 rows selected.

SQL> edit
Wrote file afiedt.buf

  1  with t as(
  2  select 1 slno,'reni' name, 1 testno from dual union all
(snip)
 19  select 24,'Thomas 3rd',5 from dual
 20  )
 21  select * from t
 22  where name in
 23          (select name from t
 24          group by name
 25*         having count(distinct (case when testno in (1,2,3,4) then testno else null end))=4)
SQL> /

      SLNO NAME           TESTNO
---------- ---------- ----------
         9 Thomas              4
         8 Thomas              3
         7 Thomas              2
         6 Thomas              1
        24 Thomas 3rd          5
        23 Thomas 3rd          4
        22 Thomas 3rd          3
        21 Thomas 3rd          2
        20 Thomas 3rd          1

9 rows selected.
By the way, I don't know whether getting 'Thomas 3rd' is correct or not.
585319
There is an elegant and (of course) mathematical solution... try

select name, sum(testno) from tests_record group by name having sum(testno) >= (1+2+3+4+...+n)

n = whatever the number of tests have been applied
n=2 s=3, n=3 s=6, n=4 s=10, n=5 s=15, n=6 s=21...

hope it helps
Aketi Jyuuzou 
SQL> with t as(
  2  select 1 as slno,'reni' as name, 1 as testno from dual
  3  union select 2,'arun',2  from dual
  4  union select 3,'jithu',3 from dual
  5  union select 4,'rajesh',4 from dual
  6  union select 5,'shiju',1 from dual
  7  union select 6,'Thomas',1 from dual
  8  union select 7,'Thomas',2 from dual
  9  union select 8,'Thomas',3 from dual
 10  union select 9,'Thomas',4 from dual
 11  union select 10,'Thomas Jr.',1 from dual
 12  union select 11,'Thomas Jr.',1 from dual
 13  union select 12,'Thomas Jr.',2 from dual
 14  union select 13,'Thomas Jr.',2 from dual
 15  union select 20,'Thomas 3rd',1 from dual
 16  union select 21,'Thomas 3rd',2 from dual
 17  union select 22,'Thomas 3rd',3 from dual
 18  union select 23,'Thomas 3rd',4 from dual
 19  union select 24,'Thomas 3rd',5 from dual)
 20  select slno,name,testno
 21  from (select slno,name,testno,
 22        max(case when testno=1 then 1 else 0 end) over(partition by name) as "has1",
 23        max(case when testno=2 then 1 else 0 end) over(partition by name) as "has2",
 24        max(case when testno=3 then 1 else 0 end) over(partition by name) as "has3",
 25        max(case when testno=4 then 1 else 0 end) over(partition by name) as "has4"
 26        from t)
 27  where "has1" * "has2" * "has3" * "has4" = 1;

SLNO  NAME           TESTNO
----  ----------  ---------
   9  Thomas              4
   8  Thomas              3
   7  Thomas              2
   6  Thomas              1
  23  Thomas 3rd          4
  24  Thomas 3rd          5
  22  Thomas 3rd          3
  21  Thomas 3rd          2
  20  Thomas 3rd          1

or

select slNO,name,testNO
  from t a
 where name in(select b.name
                 from t b
                where b.testNO in(1,2,3,4)
               group by b.name
               having count(distinct b.testNO) = 4)
order by slNO;
580343
may be this may work.



SELECT * FROM TABLENAME WHERE TESTNO IN ('1', '2', '3', '4');
430321
OP has requirement of finding whether all the tests have been attempted. Your solution will return result if student attempts any one of 1,2,3,4
587012
Hi,

This will work for you

select name,count(testno) from <table name>
group by name
having count(test)=4
587012
Hi,

Or u can try this for detail

select name,test
from xx
where name in (select name
from xx
group by name
having count(test)=4
)
order by name,test
1 - 23
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 6 2007
Added on Jun 27 2007
23 comments
12,266 views