Selecting rows by percentage match with given query

[apolishch]

Hi,

I've got a table with a large amount of columns, and would like to be able to return all those rows which match a given percentage (call it 70%) of a query. So lets say I have a table with 10 columns A, B, C, D ,E, F, G, H, I, J.

And let's say I query on all 10 columns. What I would be actually looking for is all rows where ANY 7 columns match. Obviously this can be done by amalgamating a large amount (10C7 is 120, 10C8 is 45, 10C9 is 10) of ANDed select queries via OR. Is there a better way?

Any help would be appreciated.

[joshstrike]

What comes to mind would be to have your script write out the query to do pure comparisons, which return booleans, and just add up the matches.

In other words, taking your example, I'd make the query like:

SELECT rowID, a, b, c, d, e FROM
(SELECT rowID, a, a='hello' AS amatch, b, b='world' AS bmatch, c, c='tell' AS cmatch, d, d='me' AS dmatch, e, e='something' AS ematch FROM mytable) AS subquery
WHERE amatch+bmatch+cmatch+dmatch+ematch >= 3;

That should return any rows that match 3 or more out of the five. Much faster than going through the result set iteratively afterward.