This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Jul 9, 2007 1:41 AM by 587012 RSS

Help Needed to write select statement

479546 Newbie
Currently Being Moderated
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
  • 1. Re: Help Needed to write select statement
    NicloeiW Journeyer
    Currently Being Moderated
    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>
  • 2. Re: Help Needed to write select statement
    500237 Newbie
    Currently Being Moderated
    Hi,

    You can try something like this

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

    Rgds,
    Aashish S.
  • 3. Re: Help Needed to write select statement
    479546 Newbie
    Currently Being Moderated
    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
  • 4. Re: Help Needed to write select statement
    479546 Newbie
    Currently Being Moderated
    Thanks Aashish

    But the test no is not in order in exact case
  • 5. Re: Help Needed to write select statement
    NicloeiW Journeyer
    Currently Being Moderated
    so use that query and modify your query for 100, 1000 or 10000 rows,,,,
  • 6. Re: Help Needed to write select statement
    479546 Newbie
    Currently Being Moderated
    Thanks

    You mean to write the 1000 names like tht
  • 7. Re: Help Needed to write select statement
    526547 Newbie
    Currently Being Moderated
    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.
  • 8. Re: Help Needed to write select statement
    NicloeiW Journeyer
    Currently Being Moderated
    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
  • 9. Re: Help Needed to write select statement
    ushitaki Newbie
    Currently Being Moderated
    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
      )
    ;
  • 10. Re: Help Needed to write select statement
    505805 Newbie
    Currently Being Moderated
    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.
  • 11. Re: Help Needed to write select statement
    474126 Newbie
    Currently Being Moderated
    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>
  • 12. Re: Help Needed to write select statement
    474126 Newbie
    Currently Being Moderated
    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
  • 13. Re: Help Needed to write select statement
    NicloeiW Journeyer
    Currently Being Moderated
    well i guess that to much of spoon feeding ;-)

    i am not sure if he is trying that or not ?
  • 14. Re: Help Needed to write select statement
    474126 Newbie
    Currently Being Moderated
    Nothing wrong in helping. Seems he is desperate for the help.
    Does helping going to harm us? :-)
1 2 Previous Next