tables
I’m struggling to achieve the following.
I have a table of postal codes that includes full and partial postal codes. It will have other columns also.
In another table, I have locations, all which have full postal codes. My locations table will also have other columns.
What I need to achieve is a JOIN between postal_codes and locations such that one row per location remains, where that row has the values from the postal_codes table for the row that matches the longest string of characters for the postal code.
If I only have 1 location I can achieve this with a double-nested SELECT where the innermost does an ORDER BY the length DESC and the SELECT around that does a WHERE ROWNUM =1, but this approach won’t work if I’m trying to match other locations at the same time.
I need an efficient approach as the postal_codes table has over 1 million rows.
I cannot add, remove, or alter the postal_codes table.
Any ideas would be appreciated!
user26863736 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I’d join the two tables with a like operator, and then use row_number to take the match with the longest pclookup:
SELECT loc_id, loc_postal_code, pc_id, pclookup
FROM (SELECT loc.id AS loc_id,
loc.postal_code AS loc_postal_code
pc.id AS pc_id,
pc.pclookup AS pclookup,
ROW_NUBER() OVER (PARTITION BY loc.id
ORDER BY LENGTH(pc.pclookup) DESC) AS rn
FROM locations loc
JOIN postal_codes pc ON loc.postal_code LIKE pc.pclookup || '%') t
WHERE rn = 1