Join oracle tables with ordered data

Adil 0
Viewed 23

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

Table structures:

enter image description here

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

Any help will be appreciated.

Thanks

oracle sql
1 Answers

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 || '%'
    
    ;
p3consulting0
Related