Re: distinct subset query

This way is that first find duplicated set# by modification of Relational Division, then elemenate them from the result.

Here is an example:
CREATE TABLE Equiv_Classes
(set# INTEGER NOT NULL
,element CHAR(2) NOT NULL
,PRIMARY KEY (set#, element)
);

I added some more dara:
[a],[a,b],[a,b],[a,b,c],[a,b,c,d],[a,b,c,e],[a,b,c,d,e],[a,b,c,d,e],[a,b,c,d,e]

INSERT INTO Equiv_Classes
VALUES

 (1, 'a')


,(1, 'b')

,(2, 'a')

,(2, 'b')

,(3, 'a')

,(4, 'a')

,(4, 'b')

,(4, 'c')

,(5, 'a')

,(5, 'b')

,(5, 'c')

,(5, 'd')

,(6, 'a')

,(6, 'b')

,(6, 'c')

,(6, 'e')

,(7, 'a')

,(7, 'b')

,(7, 'c')

,(7, 'd')

,(7, 'e')

,(8, 'a')

,(8, 'b')

,(8, 'c')

,(8, 'd')

,(8, 'e')

,(9, 'a')

,(9, 'b')

,(9, 'c')

Query and results:
SELECT *
  FROM Equiv_Classes R
 WHERE set# NOT IN

       (SELECT S2.set#
          FROM (SELECT set#, COUNT(*) AS element_cnt
                  FROM Equiv_Classes
                 GROUP BY
                       set#
               ) AS S1
               INNER JOIN
               (SELECT set#, COUNT(*) AS element_cnt
                  FROM Equiv_Classes
                 GROUP BY
                       set#
               ) AS S2
                 ON  S1.set#        < S2.set#
                 AND S1.element_cnt = S2.element_cnt
         WHERE NOT EXISTS
               (SELECT *
                  FROM Equiv_Classes S3
                 WHERE S3.set# = S1.set#
                   AND NOT EXISTS
                       (SELECT *
                          FROM Equiv_Classes S4
                         WHERE S4.set# = S2.set#
                           AND S3.element = S4.element
                       )
               )
       )
 ORDER BY
       R.set#, R.element

SET# ELEMENT
----------- -------

          1 a
          1 b
          3 a
          4 a
          4 b
          4 c
          5 a
          5 b
          5 c
          5 d
          6 a
          6 b
          6 c
          6 e
          7 a
          7 b
          7 c
          7 d      
          7 e      

  19 record(s) selected. Received on Sun Jul 16 2006 - 13:23:04 CEST