Re: SQL Query

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 30 Nov 2010 06:48:54 +0100
Message-ID: <4cf49045$0$6874$9b4e6d93_at_newsspool2.arcor-online.net>

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 Received on Tue Nov 30 2010 - 06:48:54 CET