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.<\/p>\n
WITH<\/span><\/span> cte<\/span> <\/span><\/span>(<\/span><\/span>bc_bug<\/span>,<\/span><\/span> bc_id<\/span>)<\/span><\/span> 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 … Continue reading “SQL Server: Common Table Expression”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[50],"_links":{"self":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/81"}],"collection":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=81"}],"version-history":[{"count":1,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/81\/revisions"}],"predecessor-version":[{"id":197,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/81\/revisions\/197"}],"wp:attachment":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=81"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=81"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=81"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nAS<\/span><\/span>
\n(<\/span><\/span>SELECT<\/span><\/span> bc_bug<\/span>,<\/span><\/span> <\/span>MAX<\/span><\/span>(<\/span><\/span>bc_id<\/span>)<\/span><\/span>
\nFROM<\/span><\/span> bug_comments<\/span>
\nWHERE LOWER(<\/span><\/span>bc_type) <\/span>=<\/span><\/span> <\/span>‘comment’<\/span><\/span> <\/span>
\nGROUP<\/span><\/span> <\/span>BY<\/span><\/span> bc_bug <\/span>)<\/span><\/span>
\nSELECT DISTINCT <\/span><\/span>
\nb.<\/span><\/span>bg_id <\/span>AS<\/span><\/span> [ID]<\/span>,<\/span><\/span> <\/span>
\nb.bg_short_desc <\/span>AS<\/span><\/span> [DESCR]<\/span>,<\/span><\/span> <\/span>
\nbc.bc_comment <\/span>AS<\/span><\/span> [COMMENT]<\/span>
\nFROM<\/span><\/span> bugs <\/span>AS<\/span><\/span> b <\/span>LEFT<\/span><\/span> <\/span>OUTER<\/span><\/span> <\/span>JOIN<\/span><\/span>
\nbug_comments <\/span>AS<\/span><\/span> bc <\/span>ON<\/span><\/span> b<\/span>.<\/span><\/span>bg_id <\/span>=<\/span><\/span> bc<\/span>.<\/span><\/span>bc_bug <\/span>LEFT<\/span><\/span> <\/span>OUTER<\/span><\/span> <\/span>JOIN<\/span><\/span>
\ncte <\/span>ON<\/span><\/span> cte<\/span>.<\/span><\/span>bc_bug <\/span>=<\/span><\/span> b<\/span>.<\/span><\/span>bg_id <\/span>AND<\/span><\/span> cte<\/span>.<\/span><\/span>bc_id <\/span>=<\/span><\/span> bc<\/span>.<\/span><\/span>bc_id <\/span>
\nWHERE <\/span><\/span>b.b<\/span>g_status <\/span>=<\/span><\/span> 1 <\/span>and<\/span><\/span>
\n((<\/span><\/span>cte<\/span>.<\/span><\/span>bc_bug <\/span>=<\/span><\/span> bugs<\/span>.<\/span><\/span>bg_id <\/span>and<\/span><\/span> cte<\/span>.<\/span><\/span>bc_id <\/span>=<\/span><\/span> bc<\/span>.<\/span><\/span>bc_id<\/span>)<\/span><\/span>
\nORDER<\/span><\/span> <\/span>BY<\/span><\/span> b<\/span>.<\/span><\/span>bg_id <\/span>DESC<\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"