Forum Stats

  • 3,872,756 Users
  • 2,266,468 Discussions
  • 7,911,296 Comments

Discussions

Help Needed to write select statement

2

Comments

  • 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 Jun 27, 2007 1:35PM
    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 Jul 8, 2007 11:26PM
    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');
This discussion has been closed.