Common Table Expressions CTE in SQL SERVER - Get Top Value of a Group

So I had a problem where one of the companies we work with wanted data for a column, however they wanted a single example.  However in our system we can have multiple entries for that column. I needed to get a single example other wise when joining would show other many more rows than necessary for what they wanted.  SO I turned to Common Table Expressions (CTE)s.

Although turned out that because I was excluding data one of my bosses said just not to send that field, because it would be similar to misinformation. Hah.


;With PBs(accountID,PBName,rn)
AS
(
select ACV.accountID, B.lastName + ', ' + B.firstName + ' ' + B.middleInit,
ROW_NUMBER() OVER (PARTITION BY acv.accountID ORDER BY acv.accountID DESC) as rn

FROM  ACV
--- etc joins

where
--conditions

group by ACV.accountID, B.lastName + ', ' + B.firstName + ' ' + B.middleInit

)
SELECT
--other fields
PB.PBName
FROM  ACV
---joins
LEFT OUTER JOIN PBs ON ACV.accountID = PBs.accountID

where
--conditions, since I am using the CTE in an outer join so I use ISNULL to set the column to 1 when there is no match, so it does not remove the columns that do not have a matching record
and ISNULL(rn,1)=1


ref
http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER

http://stackoverflow.com/questions/8403339/t-sql-join-against-a-common-table-expression-cte

http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group