Help with Select to return Duplicate rows

Can someone look at this and tell me where I went wrong? I'm trying to return all duplicate rows that have the same lastName and Address. It returns rows but they don't look like dups.

SELECT TOP (100)PERCENT dbo.tblClient.LastName, dbo.tblClientAddresses.AddressFROM dbo.tblClientINNERJOIN dbo.tblClientAddressesON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_IDGROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.AddressHAVING (COUNT(dbo.tblClientAddresses.Address) > 1)ORDER BY dbo.tblClientAddresses.Address

TOP 100 PERCENT is redundant.

SELECT dbo.tblClient.LastName, dbo.tblClientAddresses.Address,count(*)FROM dbo.tblClientINNERJOIN dbo.tblClientAddressesON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_IDGROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.AddressHAVINGCOUNT(*) > 1ORDER BY dbo.tblClientAddresses.Address

|||

ndinakar,

Is there a way to also return the Client_ID of each row?

|||

Hi Jack,

If the Client_ID is a primary key with unique values, so the duplicate rows have different Client_ID, we cannot return it.

If duplicate rows have the same Client_ID, yes, we can return it.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

try dinakar's query just add client id in select list. but dont group by client id. i think that should work.

thanks,

satish.