November 21, 2022 at 9:42 pm
I would like to know whether it is possible to join 2 tables with different sorting.
Ex:
Table 1 has, id, date, description
Table 2 has, id_det, id_mast, amt, price, dt_mev
These tables are joined with table1.id = table2.id_mast
I need to join the second table sorted by dt_mev descending order, so that the first linked row will be the latest record on table 2.
Any help is greatly appreciated.
November 21, 2022 at 10:04 pm
I need to join the second table sorted by dt_mev descending order, so that the first linked row will be the latest record on table 2.
Don't use INNER/OUTER JOIN. use CROSS APPLY.
SELECT t1.ID, t1.Date, t1.Description, ca.price
FROM t1
CROSS APPLY (SELECT TOP 1 id_det, id_mast, amt, price, dt_mev
FROM t2
WHERE t1.ID = t2.ID_det
ORDER BY t2.dt_mev DESC) ca
November 21, 2022 at 10:04 pm
Edit. cross apply may be more efficient.
Do you want to join to a single row in the second table? the one with the latest dt_mev? If so, you can add a row_number partitioned by the id_mast, ordered by the dt_mev descending. I usually include another column to make it deterministic if there is a possibility of two rows with the same date. Once you have the row_number, you can join on the id and the row_number, something like this.
SELECT a.id, a.[date], a.[description],
b.id_det, b.id_mast, b.amt,
b.price, b.dt_mev
FROM dbo.Table1 AS a
JOIN (
SELECT b.id_det, b.id_mast, b.amt,
b.price, b.dt_mev,
ROW_NUMBER() OVER (PARTITION BY id_mast ORDER BY dt_mev DESC, id_det) as rownum
FROM dbo.Table2
) AS b ON a.id = b.id_masst
and 1 = b.rownum
November 22, 2022 at 5:12 pm
Thanks for all the replies. Will check this out. I have 2 databases (SQL and Oracle) with identical tables. Hopefully this will work with Oracle too. Fingers crossed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply