Re: Query...

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 3 Jul 2007 18:16:11 +0200
Message-ID: <468a7649$0$22393$426a34cc@news.free.fr>

"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 Received on Tue Jul 03 2007 - 11:16:11 CDT