SELECT DISTINCT with primary key?

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:

Code:

---

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

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.

Quote:

---

Originally Posted by Ecniv

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

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.

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