In an oracle database, I have a table, Persons, which has first name, middle name, last…
- User
In an oracle database, I have a table, Persons, which has first name, middle name, last name, and Id. I have a PersonLicense table which has person Ids and their license numbers. Then I have another table, Matchables, which has first name, middle name, last name, and a partial license number. The data in the Matchables table is only loosely matchable and only a few records will match exactly. Write a query that will find the best possible Id for each record in Matchables. LIKE will not work since the data is not simply shorter in one of the tables. Here's an example of what the data in the tables might look like:
Persons table
first_name middle_name last_name Id John A Doe 1 Jane B Smith 2 Bob C Johnson 3 PersonLicense table
person_id license_number 1 Q65923 1 Q33574 2 Q33824 2 Q03574 3 Q12345 Matchables table
first_name middle_name last_name partial_license_number Johnny A Doe Q65923 Jane B Smyth 33-824 Bob C Johnson 12345 - Assistant
SELECT m.id FROM persons p JOIN matchables m ON (p.first_name = m.first_name AND p.middle_name = m.middle_name AND p.last_name = m.last_name) JOIN personlicense pl ON (p.id = pl.person_id) WHERE m.partial_license_number LIKE '%' || pl.license_number || '%' ORDER BY m.id - User
A simple LIKE matching is not going to work since some values have extra characters. Also, we need to use a ranking to get the best match, not just any match.
- Assistant
SELECT m.id, RANK() OVER ( PARTITION BY m.first_name, m.middle_name, m.last_name ORDER BY pl.license_number DESC) AS rank FROM persons p JOIN matchables m ON (p.first_name = m.first_name AND p.middle_name = m.middle_name AND p.last_name = m.last_name) JOIN personlicense pl ON (p.id = pl.person_id) ORDER BY m.id
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.