I'm having difficulty coming up with the right join to get my results.
I'm using PostgreSQL 7.2.x
I'm looking for a "most likely match" result. Assume you have a table with two fields,
field 1 is a serial key (unique) and field 2 is varchar.
Assume that you have the following entries in the table:
recordid val
1, 'a'
2, 'b'
3, 'ab'
And I want to match the strings "a" and "b", but not necessarily "ab", and disregard
an additional "c", and organize the result so that the records that best match are at
the top. Sample output might be:
count recordid
2 3
1 1
1 2
Record #3, containing both "a" and "b" has two count, records 1 and 2 having only one
of "a" or "b" have a count of 1.
The closest that I've come so far is from a query like
select id from table where lower(val) like lower('%a%') UNION ALL select id from table
where lower(val) like lower('%b%') UNION ALL select id from table where lower(val)
like lower(%c%');
What this gives me is
id
1
3
2
3
which is somewhat close, but then requires me to loop thru a potentially large number
of results to get the requested output.
Anybody else up to this one?
-Ben
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
