Return only the most recent records for each result

Other SQL Server Topics
1

First post.

My job role has changed rapidly since the person who created our system left and has offered no help since. In the last 9 months, I've had to learn VBA, SQL, and do my usual general IT work. I'm an SQL newbie really.

I can get my head around most basic queries, however, this one is making me loopy. Consider the code below.

SELECT
	ClientRef,
	CustomerID,
	dbo.vwFrmLegals.ActionDate As [Last Legal Date],
	LegalActionName As [Last Legal Action]
FROM
	dbo.vwFrmCustomers
	INNER JOIN dbo.vwFrmLegals ON dbo.vwFrmLegals.PlaintID = dbo.vwFrmCustomers.PlaintID
	INNER JOIN dbo.vwListLegalActions ON dbo.vwListLegalActions.LegalActionID = dbo.vwFrmLegals.LglActionID
WHERE
	(
	(dbo.vwFrmCustomers.PlaintID > 0)
	AND
	(dbo.vwFrmCustomers.FileClosed Is NULL)
	)
ORDER BY CustomerID

This returns all the data I need, and a lot I don't. I only want the most recent record returned based on the date in "Last Legal Date". I've Googled, but none of the solutions seem to work for me. When I use MAX(), I only get the one singular latest record (one record returned).

Can anyone explain to me how to achieve one record per CustomerID, which would be the one with the most recent "Last Legal Date"?

2 
;with src
as
(
	SELECT
		ClientRef,
		CustomerID,
		dbo.vwFrmLegals.ActionDate As [Last Legal Date],
		LegalActionName As [Last Legal Action],
		ROW_NUMBER() OVER (
		PARTITION BY CustomerID
		ORDER BY ActionDate DESC
	) as row_num
	FROM
		dbo.vwFrmCustomers
		INNER JOIN dbo.vwFrmLegals ON dbo.vwFrmLegals.PlaintID = dbo.vwFrmCustomers.PlaintID
		INNER JOIN dbo.vwListLegalActions ON dbo.vwListLegalActions.LegalActionID = dbo.vwFrmLegals.LglActionID
	WHERE
		(
		(dbo.vwFrmCustomers.PlaintID > 0)
		AND
		(dbo.vwFrmCustomers.FileClosed Is NULL)
		)
)
select * From src where row_num = 1
1 Like
3

Outstanding!

Thank you so much.