Forum Stats

  • 3,851,755 Users
  • 2,264,021 Discussions
  • 7,904,838 Comments

Discussions

Help Needed to write select statement

479546
479546 Member Posts: 22
edited Jul 9, 2007 4:41AM 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
«13

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 Jun 27, 2007 2:08AM
    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.
This discussion has been closed.