View Single Post
  #2 (permalink)  
Old 02-08-2007, 06:40 AM
Dimitar
 
Posts: n/a
Default ANY answer. Re: Find matches with no repeats?

In the hope it can be of any help:
Table t=(
[sql]
SELECT a, b, m(a,b) as "magic"
FROM A,B
WHERE m(a,b) > 0
[/sql])

Table t0=(
[sql]
SELECT b, max(magic) as "max_of_magic"
FROM t
GROUP BY b
[/sql]
)

Then the result would be:
[sql]
SELECT t.a, t.b
FROM t, t0
WHERE t.b = t0.b
AND t.magic = t0.max_of_magic
[/sql]

The m() function can be defined such that it returns an integer [ 0 if (a,b) does not form a match, 0 if (a,b) are neutral, and ] 0 if (a,b
) do form a match.
An important property of m() is that
m(a0,b0) = m(a1,b1) if a0=a1 and b0=b1, and m(a0,b0) != m(a0,b0) if a0!=a1 or b0!=b1.

Ideas for m():
1. m(a1,b) > m(a2,b) if
(select count(a1) from t) < (select count(a2) from t). This will increase the chances of entities with fewer matches to be included in the
result.
2. Measure how close (a,b) are to each other. An example using integers: m(a,b) = a-b, which measures the distance between the two; this ho
wever is not enough as e.g. m(1,2) = m(2,3).
Ideas 1 and 2 can be used together still not guaranteeing the uniqueness property.
Edit/Delete Message Reply With Quote