Using RowNumber()

In this example, we get the latest comment for multiple contacts:

;with cte as (
select ROW_NUMBER() OVER(PARTITION BY contactid
ORDER BY datecreated desc) AS rownum,content from comments where contactid in (
345823,175779,332941,315655,341843)
) select * from cte where rownum = 1

1. ;with cte as (...)
   Defines a CTE named cte to organize the logic before the final SELECT.

2. select ROW_NUMBER() OVER(PARTITION BY contactid ORDER BY datecreated desc) AS rownum, content
   Uses the ROW_NUMBER() window function to assign a unique row number to each comment per contactid, ordered by datecreated descending.

   This means the most recent comment for each contactid will get rownum = 1.

3. from comments where contactid in (...)
   Filters the comments table to only include rows where contactid is one of the specified values:

4. select * from cte where rownum = 1
   Selects only the rows from the CTE where rownum = 1, i.e., the most recent comment for each contactid.

;WITH CampaignTotals AS (
	SELECT CampaignId, 
	TotalSent, 
	ROW_NUMBER() OVER(PARTITION BY CampaignId ORDER BY datesent DESC) AS R 
	FROM Mailings WHERE TotalSent > 10 AND DateSent IS NOT NULL
) SELECT CampaignId, TotalSent FROM CampaignTotals WHERE R = 1;