Finding most specific prefix with SQL

Question

I have a bit of an SQL problem. Here are my tables:

areas(id, name, sla_id)
areas_groups(id, group_id, areaprefix)

The sla_id is an identifier from a different source - it is unique, but areas has its own auto-incrementing primary key.

The areaprefix field is the interesting one. It just contains the first few digits of the sla_id and is unique. Each area can only exist in one group, so the area belongs to the group with the most specific prefix. Example:

Group 12's area prefixes: 105, 110, 115, 805
Group 13's area prefixes: 1, 8

Area sla_id = 10533071 matches both group 12 (105*) and group 13 (1*)
              "105" is longer, so this area is in group 12
Area sla_id = 81031983 matches only group 13 (8*)

The reason it's done like this is so we can easily make a "catch-all" group for areas which don't fall into any other group.

I can find which group an area is in like this:

-- eg: area with sla_id 105055200
SELECT * FROM (
    SELECT group_id
    FROM areas_groups
    WHERE SUBSTR('105055200', 0, LENGTH(area_prefix)) = area_prefix
    ORDER BY LENGTH(area_prefix) DESC
)
WHERE rownum = 1;

(Did I mention this is Oracle?)

Going the other way is the tricky one: Given a group Id, I want to find all the areas which belong to that group. That is, given group 13, I want all the areas that start with 1 or 8 but not 105, 110, 115 or 805 (in this example).

The closest I've come is this:

SELECT a.id, a.sla_id, MAX(LENGTH(ag.area_prefix)), ag.group_id
FROM areas a INNER JOIN areas_groups ag
    ON (SUBSTR(a.sla_id, 0, LENGTH(ag.area_prefix)) = ag.area_prefix)
WHERE a.sla_id IS NOT NULL
GROUP BY a.id, a.sla_id, ag.group_id

That returns data like this:

 id     sla_id    leng   group_id
583    105308400    3    12
583    105308400    1    13
584    105556700    3    12
584    105556700    1    13

So if I could only grab the group_id which has the longest length for each id... I have a feeling that I'm really close but just missing a tiny little thing... Can anyone help put me out of my misery?

Answer 1

This is untested on Oracle, but I believe Oracle has supported COALESCE as a string function since version 9 so this should be OK unless you're working on an old version of Oracle.

I have assumed that there is also group of area_prefix records with two characters.

select  a.id
        ,a.sla_id
        ,coalesce(ag3.area_prefix,ag2.area_prefix,ag1.area_prefix) area_prefix
        ,coalesce(ag3.group_id,ag2.group_id,ag1.group_id) group_id
from areas a
left join areas_groups ag3
on        substr(a.sla_id,1,3) = ag3.area_prefix
left join areas_groups ag2
on        substr(a.sla_id,1,2) = ag2.area_prefix
left join areas_groups ag1
on        substr(a.sla_id,1,1) = ag1.area_prefix

Answer 2

select id
,      sla_id
,      leng
,      group_id
,      (row_number() over (partition by id order by leng desc)) rn 
from 
(
SELECT a.id, a.sla_id, MAX(LENGTH(ag.area_prefix)) leng, ag.group_id
FROM areas a INNER JOIN areas_groups ag
    ON (SUBSTR(a.sla_id, 0, LENGTH(ag.area_prefix)) = ag.area_prefix)
WHERE a.sla_id IS NOT NULL
GROUP BY a.id, a.sla_id, ag.group_id
)
where rn = 1