Re: Query...

From: metzguar <urs_at_ursmetzger.de>
Date: Tue, 03 Jul 2007 07:02:45 -0700
Message-ID: <1183471365.991317.203290@57g2000hsv.googlegroups.com>

query schrieb:
> 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...

SELECT u, e, DENSE_RANK() OVER(ORDER BY minroot) AS KEY

    FROM (SELECT u, e, MIN(root) AS minroot

              FROM (SELECT     u, e,
                               SUBSTR(SYS_CONNECT_BY_PATH(u || '@' ||
e, '\'),
                                      2,
                                      INSTR(SYS_CONNECT_BY_PATH(u ||
'@' || e, '\') || '\', '\', 2) - 2) AS root
                          FROM t1
                    CONNECT BY ((u = PRIOR u AND e > PRIOR e) OR(e =
PRIOR e AND u > PRIOR u)))
          GROUP BY u, e)

ORDER BY 1, 2; Urs Metzger Received on Tue Jul 03 2007 - 09:02:45 CDT