Join oracle tables with ordered data-CodePudding

I am in a situation where I am using like clause to join two reference tables where table structures are below.

Table structures:

I need to get the highest length matching Department Id when I do the join using like operator.

Any help will be appreciated.

Thanks

CodePudding user response：

One solution with keep( dense_rank ):

WITH departments(dept, description) AS
    (
      SELECT 'ABCD', 'Description of ABCD' FROM DUAL UNION ALL
      SELECT 'ABC', 'Description of ABC' FROM DUAL UNION ALL
      SELECT 'EFG', 'Description of EFG' FROM DUAL UNION ALL
      SELECT 'EF', 'Description of EF' FROM DUAL 
    ),
    staff(staff_id, dept) AS
    (
      SELECT 1, 'ABCDE' FROM DUAL UNION ALL
      SELECT 2, 'ABCDZ' FROM DUAL UNION ALL
      SELECT 3, 'EFGHI' FROM DUAL UNION ALL
      SELECT 4, 'EFGH' FROM DUAL 
    )
    SELECT DISTINCT s.staff_id, 
      MAX(d.dept) KEEP( DENSE_RANK FIRST ORDER BY LENGTH(d.dept) DESC) OVER(PARTITION BY s.staff_id) AS dept, 
      MAX(d.description) KEEP( DENSE_RANK FIRST ORDER BY LENGTH(d.dept) DESC) OVER(PARTITION BY s.staff_id) AS description 
    FROM staff s
    JOIN departments d ON s.dept LIKE d.dept || '%'
    
    ;