From: bens_nospam@benjamindsmith.com
To: pgsql-sql@postgresql.org
Subject: Self Join?
Date: Fri, 4 Oct 2002 14:05:11 PDT
Message-ID: <200210042105.g94L5BL25197@chico.benjamindsmith.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bens_nospam@benjamindsmith.com
Subject: Re: Self Join?
In-Reply-To: <200210042105.g94L5BL25197@chico.benjamindsmith.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox