Help Needed to write select statement

479546 · Jun 27, 2007 1:28AM

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

NicloeiW · Jun 27, 2007 1:40AM

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 · Jun 27, 2007 1:43AM

Hi,

You can try something like this

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

Rgds,
Aashish S.

479546 · Jun 27, 2007 1:45AM

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 · Jun 27, 2007 1:47AM

Thanks Aashish

But the test no is not in order in exact case

NicloeiW · Jun 27, 2007 1:49AM

so use that query and modify your query for 100, 1000 or 10000 rows,,,,

479546 · Jun 27, 2007 1:52AM

Thanks

You mean to write the 1000 names like tht

526547 · Jun 27, 2007 1:55AM

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 · Jun 27, 2007 2:06AM

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 · Jun 27, 2007 2:07AM

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 · Jun 27, 2007 2:10AM

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.

Forum Stats

Quick Links

Categories

Help Needed to write select statement

Comments

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Help Needed to write select statement

Comments