Query to find longest name of dupe records

[John Welch]

I have a table with these fields (and example data)
PersonID (long) OldRecordNum (long) FirstName(text)
12 12 Bob
12 342 Bob and Mary
18 18 Joe
18 99 Joe and Bill
19 19 Mark

I'm trying to write a query to pull the longest first name for each
personID, but am not succeeding. (I'm cleaning up a messy database and many
records have two names in first name field.) (I don't need help figuring out
why or why not to do it this way, thanks.)

I'd like the query to return the following:
Person ID FirstName
12 Bob and Mary
18 Joe and Bill
19 Mark

I appreciate any help people can give me with this.
-John

 

[Allen Browne]

How about a subquery:

SELECT PersonID,
(SELECT TOP 1 FirstName
FROM Table1 AS Dupe
WHERE Dupe.PersonID = Table1.PersonID
AND Dupe.FirstName Is Not Null
ORDER BY Len(Dupe.FirstName) DESC, Dupe.OldRecordNum)
AS LongestFirstName
FROM Table1
GROUP BY PersonID;

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[John Welch]

Thanks so much to you both! I ended up using Allen's suggestion, but they
both look like they would work. It really got me out of spinning my wheels
here at work.
-John