Forum Stats

  • 3,734,036 Users
  • 2,246,863 Discussions
  • 7,857,005 Comments

Discussions

Help Needed to write select statement

479546
479546 Member Posts: 22
edited July 2007 in SQL & PL/SQL
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
    NicloeiW Member Posts: 1,811
    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
    500237 Member Posts: 316
    Hi,

    You can try something like this

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

    Rgds,
    Aashish S.
  • 479546
    479546 Member Posts: 22
    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
    479546 Member Posts: 22
    Thanks Aashish

    But the test no is not in order in exact case
  • NicloeiW
    NicloeiW Member Posts: 1,811
    so use that query and modify your query for 100, 1000 or 10000 rows,,,,
  • 479546
    479546 Member Posts: 22
    Thanks

    You mean to write the 1000 names like tht
  • 526547
    526547 Member Posts: 93
    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
    NicloeiW Member Posts: 1,811
    edited June 2007
    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
    ushitaki Member Posts: 1,128
    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
    nurhidayat Member Posts: 736
    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
    474126 Member Posts: 914
    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
    474126 Member Posts: 914
    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
    NicloeiW Member Posts: 1,811
    well i guess that to much of spoon feeding ;-)

    i am not sure if he is trying that or not ?
  • 474126
    474126 Member Posts: 914
    Nothing wrong in helping. Seems he is desperate for the help.
    Does helping going to harm us? :-)
  • NicloeiW
    NicloeiW Member Posts: 1,811
    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
    Avinash Tripathi Member Posts: 1,614 Bronze Badge
    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
    ushitaki Member Posts: 1,128
    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
    585319 Member Posts: 511
    edited June 2007
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited July 2007
    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
    580343 Member Posts: 187
    may be this may work.



    SELECT * FROM TABLENAME WHERE TESTNO IN ('1', '2', '3', '4');
  • 430321
    430321 Member Posts: 73
    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
    587012 Member Posts: 2
    Hi,

    This will work for you

    select name,count(testno) from <table name>
    group by name
    having count(test)=4
  • 587012
    587012 Member Posts: 2
    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
This discussion has been closed.