Re: Query...

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 03 Jul 2007 23:58:11 +0200
Message-ID: <468AC673.9060802@gmail.com>

Michel Cadot schrieb:
> "query" <zzzz20007_at_gmail.com> a écrit dans le message de news: 1183434854.441612.317430_at_g37g2000prf.googlegroups.com...
> | hi,
> | i have table with users and email-address. There can be multiple
> | email-address for a user and there can be multipe users having same
> | email-address.
> | eg: Table T1:
> | u1,e1
> | u2,e2
> | u2,e3
> | u3,e3
> | u3,e5
> | u4,e2
> | u5,e1
> | u6,e4
> |
> | I need to build a table having users,email-addres and key. All users
> | having same email address should have same key and all email address
> | of these users should also have this same key. These email address may
> | be shared by other users. they also should have the same key..This
> | repeats..
> |
> | The resultant table should be like the below...
> | u1,e1,k1
> | u2,e2,k2
> | u2,e3,k2
> | u3,e3,k2
> | u3,e5,k2
> | u4,e2,k2
> | u5,e1,k1
> | u6,e4,k3
> | Please find queries to build this table
> |
> | Thanks in advance...
> |
>
> Another solution:
>
> SQL> with
> 2 data as (
> 3 select us, email,
> 4 dense_rank() over (order by us) usid,
> 5 dense_rank() over (order by email) emid
> 6 from t
> 7 ),
> 8 email as (
> 9 select emid, min(usid) min_usid
> 10 from data
> 11 group by emid
> 12 ),
> 13 us as (
> 14 select usid, min(emid) min_emid
> 15 from data
> 16 group by usid
> 17 ),
> 18 ouf as (
> 19 select d.us, d.email, d.usid, d.emid, e.min_usid, u.min_emid,
> 20 case when d.emid = u.min_emid and d.usid = e.min_usid
> 21 then rownum end grp
> 22 from data d, us u, email e
> 23 where u.usid = d.usid
> 24 and e.emid = d.emid
> 25 )
> 26 select us, email,
> 27 max(grp) over (order by min_usid, min_emid, usid, emid) id
> 28 from ouf
> 29 order by us, email
> 30 /
> US EMAIL ID
> ---------- ---------- ----------
> u1 e1 2
> u2 e2 4
> u2 e3 4
> u3 e3 4
> u3 e5 4
> u4 e2 4
> u5 e1 2
> u6 e4 6
>
> 8 rows selected.
>
> Regards
> Michel Cadot
>
>
>

Michel, i think, this problem has too recursive roots, to be solved with analytics only - imho, connect by or model is a unavoidable. Consider the following dataset, where both, query provided by you and by Urs yields wrong results (in opposite to mentioned Volder's query)...

SQL> select us, email, dense_rank() over (order by new_us) key

   2 from (select us, email, min(min(cbr)) over (partition by us) new_us

   3              from (select tt.*, connect_by_root(us) cbr
   4                      from (select t.*, prior email pr_email
   5                              from (select t.*,
   6                                           count(1) over(partition 
by email) cnt
   7                                      from t) t
   8                            connect by nocycle prior us = us
   9                                   and prior email <> email
  10                                   and cnt > 1
  11                                   and prior cnt > 1) tt
  12                    connect by nocycle(prior us <> us
  13                                   and prior email = email)
  14                            or (prior us = us and prior email = 
pr_email))
  15             group by us, email)
  16     order by 1, 2

  17 /

US EMAIL KEY
--- ----- ---

u1  e1      1
u10 e12     2
u10 e5      2
u2  e2      2
u2  e3      2
u3  e3      2
u3  e5      2
u4  e2      2
u5  e1      1
u6  e4      3
u7  e12     2
u7  e8      2
u8  e7      2
u8  e8      2

Best regards

Maxim Received on Tue Jul 03 2007 - 16:58:11 CDT