filter dataTable/datagridview to show top 1 row for duplicate IDs?

[Rich]

Hello,

I am just checking if there is a property or technique for displaying or
retrieving from a dataTable the top 1 row(s) for rows containing duplicate
keys (IDs). I have to pull data from a sql server DB. The resultset
contains 10 distinct rows but the keys are duplicate - like record 12345 is
listed twice but distinct because some of the other data for each field is
different. I only need to see one of the 2 rows for ID 12345 (and for ID
32234...). I tried a select statement like "Select Top 1 * From (Select
.....Where t1.ID = t2.ID...). A query like this works OK for small source
data, but my source tables contain hundreds of thousands of rows - so the
query just kept running without returning any data. If I do a straight
forward "Select ....) without the subquery I get my 10 rows in .5 seconds.

So before I write a bunch of spaghetti code to retrieve the top 1 row from
my result data table for each duplicate ID, is there a property/technique to
do this? Is it possible to write a sql type Select statement against a
VB.Net dataTable?

My spaghetti code will be something like I will store each ID in an array.
I will loop through the array and pull only one row for that ID into another
dataTable until I have my 5 Top 1 rows of the original 10. Is this my only
option or is there a better way/method?

Thanks,
Rich

[Cor Ligthert [MVP]]

Rich,

For version 2005 is for that a new overloaded DataView.ToTabl e method with a
boolean that tells if it has to be used as distinct.

It is in one of these links
http://windowssdk.msdn.microsoft.com.../wec2b2e6.aspx

For the other versions we have a sample on our website.

http://www.vb-tips.com/dbpages.aspx?Search=distinct

Cor

"Rich" <Ri**@discussio ns.microsoft.co mschreef in bericht
news:26******** *************** ***********@mic rosoft.com...

Hello,

I am just checking if there is a property or technique for displaying or
retrieving from a dataTable the top 1 row(s) for rows containing duplicate
keys (IDs). I have to pull data from a sql server DB. The resultset
contains 10 distinct rows but the keys are duplicate - like record 12345
is
listed twice but distinct because some of the other data for each field is
different. I only need to see one of the 2 rows for ID 12345 (and for ID
32234...). I tried a select statement like "Select Top 1 * From (Select
....Where t1.ID = t2.ID...). A query like this works OK for small source
data, but my source tables contain hundreds of thousands of rows - so the
query just kept running without returning any data. If I do a straight
forward "Select ....) without the subquery I get my 10 rows in .5 seconds.

So before I write a bunch of spaghetti code to retrieve the top 1 row from
my result data table for each duplicate ID, is there a property/technique
to
do this? Is it possible to write a sql type Select statement against a
VB.Net dataTable?

My spaghetti code will be something like I will store each ID in an array.
I will loop through the array and pull only one row for that ID into
another
dataTable until I have my 5 Top 1 rows of the original 10. Is this my
only
option or is there a better way/method?

Thanks,
Rich

[Brian Tkatch]

Rich wrote:

Hello,

I am just checking if there is a property or technique for displaying or
retrieving from a dataTable the top 1 row(s) for rows containing duplicate
keys (IDs). I have to pull data from a sql server DB. The resultset
contains 10 distinct rows but the keys are duplicate - like record 12345 is
listed twice but distinct because some of the other data for each field is
different. I only need to see one of the 2 rows for ID 12345 (and for ID
32234...). I tried a select statement like "Select Top 1 * From (Select
....Where t1.ID = t2.ID...). A query like this works OK for small source
data, but my source tables contain hundreds of thousands of rows - so the
query just kept running without returning any data. If I do a straight
forward "Select ....) without the subquery I get my 10 rows in .5 seconds.

So before I write a bunch of spaghetti code to retrieve the top 1 row from
my result data table for each duplicate ID, is there a property/technique to
do this? Is it possible to write a sql type Select statement against a
VB.Net dataTable?

My spaghetti code will be something like I will store each ID in an array.
I will loop through the array and pull only one row for that ID into another
dataTable until I have my 5 Top 1 rows of the original 10. Is this my only
option or is there a better way/method?

Thanks,
Rich

In SQL server can't you just:

SELECT * FROM table GROUP BY key?

B.