RE: SQL Question

Hi Jaydas,
Thanks for the reply.
It gives me a good starting point to go with. The query handles cases where there are multiple rows.
For eg: U=2006 has G=1010 and S=1013. If there were another U=2010 with same G=1010, then a rum through
the query would generate a S=1012 for this combination also as the min checks for group_id in () and that will
evaluate any U belonging to a single group that is part of the multiple groups that a U belongs to.

But I will take this query as a starting point and will work on getting that resolved.
Thanks for your time and appreciate your help Regards,
Madhavan
http://www.dpapps.com

On Thu, 03 Apr 2003 12:28:42 -0800, "Chelur, Jayadas {PBSG}" <jayadas.chelur_at_pepsi.com> said:
> Madhavan,
>
> I have created a similiar table and inserted the data
> as follows :-
>
> =====================================================
>
> CREATE TABLE UT
> (
> U NUMBER(4),
> S NUMBER(4),
> G NUMBER(4)
> );
>
> INSERT INTO UT VALUES(2005,1012,1010);
> INSERT INTO UT VALUES(2005,1012,1011);
> INSERT INTO UT VALUES(2006,1013,1010);
> INSERT INTO UT VALUES(2007,1017,1016);
> INSERT INTO UT VALUES(2008,1018,1010);
> INSERT INTO UT VALUES(2008,1018,1011);
>
> INSERT INTO UT VALUES(2009,1019,1016);
> INSERT INTO UT VALUES(2001,1020,1010);
> INSERT INTO UT VALUES(2001,1020,1011);
>
> COMMIT;
>
> ===========================================================
>
> this query will identify all the security groups and the
> minimum security group id of the "identical" one ...
>
>
> SELECT DISTINCT
> S2.S ORIGINAL_SG, /* original security group */
> S3.S EQUIV_SG /* equivalent security group */
> FROM (
> SELECT S, COUNT(*) RECS
> FROM UT
> GROUP BY S
> ) S1, /* security groups and their group counts - table1 */
> (
> SELECT S, COUNT(*) RECS
> FROM UT
> GROUP BY S
> ) S2, /* security groups and their group counts - table2 */
> (
> SELECT DISTINCT S
> FROM UT
> ) S3 /* just the unique security groups */
> WHERE S1.RECS = S2.RECS /* match the sec. groups with the same record
> counts */
> AND S1.S <> S2.S /* make sure they are NOT the same security
> group */
> AND NOT EXISTS /* make sure they include identical group ids
> */
> (
> SELECT G FROM UT WHERE S = S1.S
> MINUS
> SELECT G FROM UT WHERE S = S2.S
> )
> AND S3.S = ( /* see note */
> SELECT MIN(S)
> FROM UT
> WHERE G IN
> (
> SELECT G
> FROM UT
> WHERE S = S1.S
> )
> )
>
> /* note :
> this is to find the minimum value of the security id which has the same
> group
> id records as that any of the matching security groups. this minimum
> value
> can
> be used to update the security group ids of all other identical security
> groups
> at a later point of time
> */
>
> ============================================================================
> ====
>
> you can either change the query to update all the eligible security id to
> their corresponding minimum values or generate equivalent update
> statements
> using this query and run them as a batch ...
>
> HTH ...
>

-- 
Madhavan Amruthur
DecisionPoint Applications

-- 
http://www.fastmail.fm - Choose from over 50 domains or use your own
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Madhavan Amruthur
  INET: mad5698_at_fastmail.fm

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).