Scenario: Need to write a query based on two tables. The tables have a one-to-many relationship. The “bug_comments” table can have many records for one table in the “bug” table. However, I do not want all of the records in the “bug_comments” table, I only want the most recent record. Also, I don’t want to write a subquery or use a cursor.
WITH cte (bc_bug, bc_id)
AS
(SELECT bc_bug, MAX(bc_id)
FROM bug_comments
WHERE LOWER(bc_type) = ‘comment’
GROUP BY bc_bug )
SELECT DISTINCT
b.bg_id AS [ID],
b.bg_short_desc AS [DESCR],
bc.bc_comment AS [COMMENT]
FROM bugs AS b LEFT OUTER JOIN
bug_comments AS bc ON b.bg_id = bc.bc_bug LEFT OUTER JOIN
cte ON cte.bc_bug = b.bg_id AND cte.bc_id = bc.bc_id
WHERE b.bg_status = 1 and
((cte.bc_bug = bugs.bg_id and cte.bc_id = bc.bc_id)
ORDER BY b.bg_id DESC