Hey all,

Must be Monday because I can't get my brain around this one.

I'm trying to extract a unique column from a parent table (tab1)
having the number of child records (tab2) exceed a certain count. I
used a count of 2 in my example and so far I've got the following:


select unique a.rec_num,count(a.rec_size)
from tab2 a,
having count(a.rec_size) >2
group by a.rec_num

See Data section at bottom:


My output was correctly:
1,3
3,3


Now that I know the rec_nums (1,3) that have > 2 count records and
their count values. How do I find within each of the 3 records of
rec_num=1 and rec_num=3 that have a rec_size the same that are equal?
Essentially, I want to find duplicate records of rec_num and rec_size
but ONLY where the count is > 2.

So my answer should be:
3,20

- as this satisfies both criteria of having a count >2 within each
rec_num and rec_size values that are equal.



Am I missing something obvious? Sorry if this is trivial for the
experts but my SQL writing days ended with Oracle 7.


Thanks,
Cliff






-----------------------------------------------------------------{DATA}--------------------------------------

table:
tab1
column:

rec_num
------------------------
1
2
3

table: tab2
column: rec_num
column: rec_size
________________
1,20
1,30
1,40
2,20
3,20
3,30
3,20

On 30.11.2010 03:05, Cliff wrote:
One of possibilities could be analytics (assuming , your Oracle version
is 8.1.6 and above )

SQL> with tab2 as (
2 select 1 rec_num,20 rec_size from dual union all
3 select 1,30 from dual union all
4 select 1,40 from dual union all
5 select 2,20 from dual union all
6 select 3,20 from dual union all
7 select 3,30 from dual union all
8 select 3,20 from dual
9 ),
10 tab1 as (
11 select 1 rec_num from dual union all
12 select 2 from dual union all
13 select 3 from dual
14 )
15 select unique rec_num,rec_size from (
16 select t2.*,
17 count(*) over(partition by t2.rec_num) crn,
18 count(*) over(partition by t2.rec_num,t2.rec_size) crs
19 from tab1 t1,tab2 t2
20 where t1.rec_num=t2.rec_num
21 )
22 where crn>2 and crs>1
23 /

REC_NUM REC_SIZE
---------- ----------
3 20

Note, that "WITH" clause is used only to model your data and is not
necessary for your purpose ( it won't work if your are indeed on Oracle 8i).

Best regards

Maxim

On 2010-11-30 00:48:54 -0500, Maxim Demenko said:


table:
tab1
column:
rec_num
------------------------
1
2
3

table:

The database is actually 10.x , it was just my SQL knowledge was stuck
back at version 7. I'll give it a try.

Pologista (Thanks) Again