I’ve had tons of people ask me how to Select DISTINCT rows from a DataSet. Why not use SQL? Well sometimes you just can’t, and sometimes its much more efficient to do it webserver side then database side.

For some reason Microsoft has an article on how to write your own helper class, which is fine and dandy, but you don’t need to bother. While this implementation isn’t exactly the same as a SQL call, its very simple and works extremely well when working with medium to small datasets.

Say we have a DataSet ds like so:

recordID groupID value
1 100 abc
2 100 def
3 220 ghi
4 333 jkl

So to select distinct groupIDs we simply:

DataTable distinctDT = ds.Tables[0].DefaultView.ToTable(true, new string [] { "groupID" });

This returns:

groupID
100
220
333

We can now use this distinct DataTable to make our queries on our original DataSet:

ds.Tables[0].Select("groupID = " + distinctDT.Rows[0]["groupID"].toString());

That looks a little more complicated then it actually is… oh well, its actually quite a nice solution when you can’t use SQL to do your DISTINCT selects for you because you can use the same DataSet over and over reducing time to your SQL Server or FileIO.

Tagged as: , , ,