Re: SQL Query
From: Cliff <nobody_at_nowhere.com>
Date: Tue, 30 Nov 2010 06:21:18 -0500
Message-ID: <2010113006211881534-nobody_at_nowherecom>
>> 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
Date: Tue, 30 Nov 2010 06:21:18 -0500
Message-ID: <2010113006211881534-nobody_at_nowherecom>
On 2010-11-30 00:48:54 -0500, Maxim Demenko said:
> On 30.11.2010 03:05, Cliff wrote:
>> 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
>>
> > 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
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 Received on Tue Nov 30 2010 - 12:21:18 CET