CODEWITHSUNDEEP
sqloracle-database
hinotf
hinotf

Reputation: 1138

Join with MAX date in range

I have two tables: DEAL and DEPTH (history of departments)

SELECT *
  FROM deal
       LEFT JOIN depth
         ON (    deal.dept_gid = depth_gid
             AND deal.deal_begin_date <= depth_end_date
             AND deal.deal_end_date >= depth_start_date)

But if there more that one row in DEPTH that meets condition "same dept_gid and ranges must intersect" then I need to return row with maximum depth_end_date. How can I do it?

Upvotes: 0

Views: 62

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

Try using row number here:

SELECT *
FROM
(
    SELECT d1.*, d2.*,
        ROW_NUMBER() OVER (PARTITION BY d1.dept_gid ORDER BY d2.depth_end_date DESC) rn
    FROM deal d1
    LEFT JOIN depth d2
        ON d1.dept_gid = d2.depth_gid AND
           d1.deal_begin_date <= d2.depth_end_date AND
           d1.deal_end_date >= d2.depth_start_date
) t
WHERE rn = 1

Upvotes: 2

Related Questions