Q&A for speakers of other languages learning English
WITH max_comment_dates AS ( SELECT postid, MAX(creationdate) AS max_creationdate FROM comments WHERE text IS NOT NULL GROUP BY postid ), latest_comments AS ( SELECT c.postid, c.text AS latest_comment FROM comments c JOIN max_comment_dates mcd ON c.postid = mcd.postid AND c.creationdate = mcd.max_creationdate WHERE c.text IS NOT NULL -- Tiebreaker: select the comment with the highest ID in case of ties on creationdate AND c.id = ( SELECT MAX(inner_c.id) FROM comments inner_c WHERE inner_c.postid = c.postid AND inner_c.creationdate = c.creationdate ) ) SELECT count(*) FROM latest_comments; -- total comments 30396666 -- total comments 30396780 old query -- 75632691 -- 76079476 -- 28645383