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.

Help with Parameterized query building dataset

I have a class that works fine using the SQLDataReader but when I try and duplicate the process using a Dataset instead of a SQLDataReader it returnsa a null value.

This is the code for the Method to return a datareader

public

SqlDataReader GetOrgID()

{

Singleton s1 =Singleton.Instance();Guid uuid;

uuid =

newGuid(s1.User_id);SqlConnection con =newSqlConnection(conString);string selectString ="Select OrgID From aspnet_OrgNames Where UserID = @.UserID";SqlCommand cmd =newSqlCommand(selectString, con);

cmd.Parameters.Add(

"@.UserID",SqlDbType.UniqueIdentifier, 16).Value = uuid;

con.Open();

SqlDataReader dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

return dtr;

This is the code trying to accomplish the same thing with a Dataset instead.

publicDataSet organID(DataSet dataset)

{

Singleton s1 =Singleton.Instance();Guid uuid;

uuid =

newGuid(s1.User_id);string queryString ="Select OrgID From aspnet_OrgNames Where UserID = @.UserID";SqlConnection con =newSqlConnection(conString);

SqlCommand cmd =newSqlCommand(queryString, con);

cmd.Parameters.Add(

"@.UserID",SqlDbType.UniqueIdentifier, 16).Value = uuid;

SqlDataAdapter adapter =newSqlDataAdapter();

adapter.SelectCommand = cmd;

adapter.Fill(dataset);

return dataset;

}

Assume that the conString is set to a valid connection string. The Singlton passes the userid in from some code in the code behind page ...this functionality works as well.

So assume that the Guid is a valid entry..I should return a valid dataset but its null.

Got my answer on another board

http://forums.asp.net/1597763/ShowThread.aspx#1597763

|||

hi prysson,

i saw that link and its exactly the same question and posted by yourself only, you asked the same question and answered to yourself.