SELECT DISTINCT with primary key?

[Mike Hildner]

MS SQL Server 2000:

I'm trying to get a list of distinct columns, but I also need the primary key. I thought something like this might work:

SELECT a.CmnPersonPK, b.LastName, b.FirstName, b.MiddleName, b.DOB FROM vCmnPerson a
INNER JOIN (SELECT CmnPersonPK, LastName, FirstName, MiddleName, DOB FROM vCmnPerson GROUP BY LastName, FirstName, MiddleName, DOB) AS b
ON a.CmnPersonPK = b.CmnPersonPK

but I get an error "Column 'vCmnPerson.CmnPersonPK' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Been trying and searching, can't figure it out. How do I get a list where those four columns are distinct, but also retrieve the PK?

Thanks,
Mike

[Ecniv]

You'd have to add it to the group by, but this would return each one as they are unique.

You could use a sub query to get the grouping then link back to the main table to retrieve the primary key... Depending on the sub query and function you'd need (probably) is First... or Last...

Or ask Szlamany as he is great with Sql server.

[Mike Hildner]

You could use a sub query to get the grouping then link back to the main table to retrieve the primary key... Depending on the sub query and function you'd need (probably) is First... or Last...

Ok, but I'm still lost. Can you give me an example?

Thanks,
Mike

[szlamany]

How can you get a DISTINCT list and also get the primary keys?

Are you looking for DUPLICATES - situations where the LastName, FirstName, MiddleName and DOB are on file more then once?

Please explain your goal a bit further.

[Mike Hildner]

Yeah, you're right. I think what I was trying to do didn't make sense. I guess what I was wanting was a list of unique column values, and an PK, just so I could attach to it.

I'm going to go about this a different way. Thanks.

Mike