1 2 Previous Next 23 Replies Latest reply: Jul 9, 2007 3:41 AM by 587012 RSS

    Help Needed to write select statement

    479546
      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
          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
            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
              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
                Thanks Aashish

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

                    You mean to write the 1000 names like tht
                    • 7. Re: Help Needed to write select statement
                      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.
                      • 8. Re: Help Needed to write select statement
                        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
                        • 9. Re: Help Needed to write select statement
                          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
                            )
                          ;
                          • 10. Re: Help Needed to write select statement
                            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.
                            • 11. Re: Help Needed to write select statement
                              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>
                              • 12. Re: Help Needed to write select statement
                                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
                                • 13. Re: Help Needed to write select statement
                                  NicloeiW
                                  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
                                    Nothing wrong in helping. Seems he is desperate for the help.
                                    Does helping going to harm us? :-)
                                    1 2 Previous Next