Subject Re: [firebird-support] Finding best match Svein Erling Tysvaer 2006-10-13T05:46:30Z
What about something like

SELECT UID, COALESCE(CASE WHEN FAMET = :FAMET2 THEN 4 ELSE 0 END) +
COALESCE(CASE WHEN FALLYKSUS = :FALLYKSUS2 THEN 3 ELSE 0 END) +
COALESCE(CASE WHEN FOSAKOND = :FOSAKOND2 THEN 2 ELSE 0 END)
AS PRIORITY
FROM TAB_T
WHERE TSOON = :TSOON
AND (FAMET = :FAMET OR FAMET IS NULL)
AND (FALLYKSUS = :FALLYKSUS OR FALLYKSUS IS NULL)
AND (FOSAKOND = :FOSAKOND OR FOSAKOND IS NULL)
ORDER BY 2 DESC

If all match, you'll get PRIORITY 9 (maximum), if all are NULL, you'll
get PRIORITY 0 (minimum for a match, and at the bottom of the result
set), if some match you'll get something in between.

I guessed that matching FOSAKOND and FALLUKSUS with FAMET NULL would be
better than matching FAMET with FOSAKOND AND FALLUKSUS both NULL, if not
you'll have to change the values (e.g. CASE FALLYKSUS = 2 and FOSAKOND = 1).

HTH,
Set

ainpoissee wrote:
> Hi,
>
> I have table like
>
> CREATE TABLE TAB_T (
> UID INTEGER PRIMARY KEY,
> Tsoon INTEGER NOT NULL,
> FOsakond INTEGER,
> FAllyksus INTEGER,
> FAmet INTEGER
> );
>
> and I want to get UID for a given Tsoon value which best matches with
> remaining fields. Ie given Tsoon = 10, FAllyksus = 1, FOsakond = 2,
> FAmet = 3, if there is a row with these values then I want it's UID.
> In case there isn't exact match, next best would be one where FOsakond
> IS NULL but other columns do match.
> If there isn't such one, then I want one where FAllyksus IS NULL but
> FOsakond and FAmet do match. And so on... is there a way to make such
> a query or do I have to try one by one till I find combination which
> exists?
> Priority of the fields is FAmet, FAllyksus, FOsakond, ie it is better
> to have matching FAmet (with FAllyksus and FOsakond NULL) than
> matching FAllyksus (with FAmet and FOsakond NULL).
>
>
> TIA
> ain