Datatable filtering

[k3n51mm]

I have a datatable that I need to filter. It's a rather complex filter though (for me anyway), and I'm having serious trouble getting my head around datacolumn filtering and all that. Note that i'm only doing this in VB because the database we must use (SQL Compact Edition) does not support the complex join/subquery required to get this data directly from the db.

Given the datatable:

NAME RESULT COMPVALUE
abcd 8 6
efgh 3 7
lmno 9 2
abcd 4 6
lmno 5 2
efgh 4 7
abcd 9 6

I need the following result set, preferably in a new datatable:
abcd 9 6
efgh 4 7
lmno 9 2

For each NAME in the datatable, I need to return the highest RESULT and its COMPVALUE. COMPVALUE is a static value for each NAME and will always be the same.

Trying to break it down, I figured I'd need to get all the rows for each NAME, get the highest RESULT, grab the VALUE, and add it to the output datatable. But when I start looking at the datacolumn filtering docs on MSDN, I just can't understand it all at first glance. Little help?

 

[cjard]

Im amazed to hear that sqlserver compact cannot do this:

SELECT a.*
FROM
  table a
  INNER JOIN
  (SELECT name, max(result) FROM table GROUP BY name) b
  ON a.name = b.name AND a.result = b.result