LINQ to SQL - selecting a list of the highest version numbers...

Question

I am trying to write some Linq to SQL that will bring me back a list of rows containing a single instance of BaseQuestionIDs but I want the row with the highest version, so, from the following table of data:

Id  CreatedDate                 Version    BaseQuestionID
 2  2009-10-07 13:47:27.687         1    2
 3  2009-10-07 13:49:35.010         1    3
 4  2009-10-09 16:03:45.973         1    5
 5  2009-10-16 16:01:33.603         2    5
 6  2009-10-16 16:16:22.680         1    6
 7  2009-10-16 16:16:53.807         2    6
 8  2009-10-16 16:41:31.180         3    6
 9  2009-10-19 08:06:23.210         4    6

I want to return:

Id  CreatedDate                 Version    BaseQuestionID
 2  2009-10-07 13:47:27.687         1    2
 3  2009-10-07 13:49:35.010         1    3
 5  2009-10-16 16:01:33.603         2    5
 9  2009-10-19 08:06:23.210         4    6

I have tried a variety of things, but have not nailed it yet. I am working in vb.net, but am happy to try and translate from c# if that's what you know... Any pointer would be greatly appreciated.

Answer 1

How about (C#):

from q in dc.Questions
group q by q.BaseQuestionID into grouped
select grouped.OrderByDescending(q => q.Version).First()

So basically you form groups of rows by question ID:

Id  CreatedDate                 Version    BaseQuestionID
 2  2009-10-07 13:47:27.687             1          2

 3  2009-10-07 13:49:35.010             1          3

 4  2009-10-09 16:03:45.973             1          5
 5  2009-10-16 16:01:33.603             2          5

 6  2009-10-16 16:16:22.680             1          6
 7  2009-10-16 16:16:53.807             2          6
 8  2009-10-16 16:41:31.180             3          6
 9  2009-10-19 08:06:23.210             4          6

(each block is one "grouped row") and then order each group by version descending, taking only the first row:

Id  CreatedDate                 Version    BaseQuestionID
 2  2009-10-07 13:47:27.687             1          2
 3  2009-10-07 13:49:35.010             1          3
 5  2009-10-16 16:01:33.603             2          5
 9  2009-10-19 08:06:23.210             4          6

I would hope that will get a sensible translation into SQL, but I wouldn't like to say for sure :)