By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,983 Members | 1,582 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,983 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Sep 27 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.../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**@discussions.microsoft.comschreef in bericht
news:26**********************************@microsof t.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

Sep 28 '06 #2

P: n/a

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.

Sep 28 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.