The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
SELECT A.Col1, A.Col2, B.*
FROM TABLE_A AS A
OUTER APPLY
(
SELECT
VB.INVREF,
VB.INVDATE
FROM VW_B AS VB
WHERE VB.ITEMID = A.ITEMID
AND VB.INVDATE = (SELECT MAX(INVDATE) FROM VW_B WHERE INVREF = VB.INVREF)
) AS B
SELECT A.Col1, A.Col2, B.*
FROM TABLE_A AS A
OUTER APPLY
(SELECT TOP 1 VB.INVREF, VB.INVDATE
FROM VW_B AS VB
WHERE VB.ITEMID = A.ITEMID
ORDER BY VB.INVDATE DESC ) AS B
SELECT
A.Col1,
A.Col2,
B.*
FROM
TABLE_A AS A
OUTER APPLY
(
SELECT TOP 1
VB.INVREF,
VB.INVDATE
FROM
VW_B AS VB
WHERE
VB.ITEMID = A.ITEMID
ORDER BY
VB.INVDATE DESC
) AS B
SELECT A.Col1, A.Col2, B.*
FROM TABLE_A AS A
CROSS APPLY
(SELECT TOP 1 VB.INVREF, VB.INVDATE
FROM VW_B AS VB
WHERE VB.ITEMID = A.ITEMID
ORDER BY VB.INVDATE DESC ) AS B
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join the community of 500,000 technology professionals and ask your questions.