Re: SQL Query

From: Cliff <nobody_at_nowhere.com>
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