Re: distinct subset query
Date: 15 Jul 2006 21:14:40 -0700
Message-ID: <1153023280.151674.125070_at_m73g2000cwd.googlegroups.com>
This is a bit rough and untested, so forgive me
CREATE TABLE Equiv_Classes
The OUTER UNION is part of SQL-92, but nobody implements it. Pretend
with me (you can fake it with UNION and EXCEPT operators
SELECT S1.set_nbr, S2.set_nbr
(set_nbr INTEGER NOT NULL,
element_name CHAR(2) NOTB NULL,
PRIMARY KEY (set_nbr, element_name));
FROM Equiv_Classes AS S1, Equiv_Classes AS S2
WHERE S1.set_nbr <= S2.set_nbr
AND NOT EXISTS
(SELECT set_nbr, element_name
FROM Equiv_Classes AS S3
WHERE S3.set_nbr = S1.set_nbr
OUTER UNION
SELECT set_nbr, element_name
FROM Equiv_Classes AS S4
WHERE S4.set_nbr = S2.set_nbr
ON S4.set_nbr <> S3.set_nbr);
The NOT EXISTS() says that the two different sets are equal because
they have no elements outside either of them. So if sets 1,2,3,4 were
identical, I would get (1,1), (2,2) (3,3), (4,4), (1,2), (1,3),
(1,4), (2,3), (2,4) in my output.
While that is right, I woufl probably want to represent the equivalent classes as { (1,2), (1,3), (1,4)} with set_nbr = 1 as defining value for the class.
There ought to be some clever way to use EXCEPT, UNION and INTERSECT to do this, but I need my sleep. Received on Sun Jul 16 2006 - 06:14:40 CEST