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

27 Sep 2006 6:19 PM

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

28 Sep 2006 3:57 AM

Cor Ligthert [MVP]

Rich,

For version 2005 is for that a new overloaded DataView.ToTable 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/en-us/library/wec2b2e6.aspx

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

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

Cor

Show quoteHide quote

"Rich" <R***@discussions.microsoft.com> schreef in bericht
news:26A72069-D8B6-41C9-81C8-ADAAFC26F454@microsoft.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

28 Sep 2006 2:03 PM

Brian Tkatch

Rich wrote:
Show quoteHide quote

> 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.