You can do this using generic SQL with group by
:
SELECT C.Content_ID, C.Content_Title, MAX(M.Media_Id)
FROM tbl_Contents C LEFT JOIN
tbl_Media M
ON M.Content_Id = C.Content_Id
GROUP BY C.Content_ID, C.Content_Title
ORDER BY MAX(C.Content_DatePublished) ASC;
Or with a correlated subquery:
SELECT C.Content_ID, C.Contt_Title,
(SELECT M.Media_Id
FROM tbl_Media M
WHERE M.Content_Id = C.Content_Id
ORDER BY M.MEDIA_ID DESC
LIMIT 1
) as Media_Id
FROM tbl_Contents C
ORDER BY C.Content_DatePublished ASC;
Of course, the syntax for limit 1
varies between databases. Could be top
. Or rownum = 1
. Or fetch first 1 rows
. Or something like that.