>Hi everyone!
>
>This might not be a straightforward Firebird question, but I'm hoping there's
>a feature I'm unaware of that can help me beyond plain-vanilla SQL.
>
>I have two tables. The first is a list of names of "critical parameters," and
>the second relates certain object IDs, critical parameter names, and critical
>parameter values:
[removed CRITICALPARAMS definition since I don't think it is of importance to
the question]
>CREATE TABLE CRITICALPARAMVALS
>(
>ID INTEGER NOT NULL,
>PARAM Varchar(32) NOT NULL,
>VAL Float NOT NULL,
>CONSTRAINT PK_CRITICALPARAMVALS_1 PRIMARY KEY (DATAPOINTHASH,PARAM)
>);
>
>insert into CRITICALPARAMVALS values (1, 'a', 0.0);
>insert into CRITICALPARAMVALS values (1, 'b', 0.0);
>insert into CRITICALPARAMVALS values (1, 'c', 2.0);
>insert into CRITICALPARAMVALS values (1, 'foo', 99.0);
>insert into CRITICALPARAMVALS values (2, 'a', 0.0);
>insert into CRITICALPARAMVALS values (2, 'b', 0.0);
>insert into CRITICALPARAMVALS values (2, 'c', 2.0);
>insert into CRITICALPARAMVALS values (2, 'foo', 99.0);
>insert into CRITICALPARAMVALS values (3, 'a', 0.0);
>insert into CRITICALPARAMVALS values (3, 'b', 0.0);
>insert into CRITICALPARAMVALS values (3, 'c', 1.0);
>insert into CRITICALPARAMVALS values (3, 'foo', 98.0);
>insert into CRITICALPARAMVALS values (4, 'a', 0.0);
>insert into CRITICALPARAMVALS values (4, 'b', 0.0);
>insert into CRITICALPARAMVALS values (4, 'c', 1.0);
>insert into CRITICALPARAMVALS values (4, 'foo', 98.0);
>insert into CRITICALPARAMVALS values (5, 'a', 0.0);
>insert into CRITICALPARAMVALS values (5, 'b', 0.0);
>insert into CRITICALPARAMVALS values (5, 'c', 2.0);
>insert into CRITICALPARAMVALS values (5, 'foo', 98.0);
>
>The problem is to partition the critical parameter space, grouping together
>all object IDs that have the same parameter values.
>We can think of using a "seed" object ID, and asking what other IDs belong to
>the same partition as the seed object.
>
>In our example, objects 1 and 2 form a partition, 3 and 4 form another, and 5
>forms a third.
>All five objects are equal in the critical parameters a and b, but differ in
>parameters c and foo.
>
>Is there any way to solve this using plain-vanilla SQL? How about a recursive
>CTE?
Can the question be rephrased as you being interested in which sets are
identical, Elias? If I understand things correctly, I would assume "double
negation" to be the "simple" answer you're looking for:
with tmp(id) as
(select distinct id from CRITICALPARAMVALS)
select t.id, t2.id
from tmp t
join tmp t2 on t.id < t2.id
where not exists(select * from CRITICALPARAMVALS a
where t.id = a.id
and not exists(select * from CRITICALPARAMVALS b
where t2.id = b.id
and a.PARAM=b.param and (exists (select
isequal from TEST_FLOAT_EQ(a.val, b.val, 1e-5) where ISEQUAL=1))))
At least, I guess (haven't tested anything, just know that "double negation"
has been an answer to "set equality" before) this query would return two rows
like this:
1 2
3 4
HTH,
Set
