current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I want to query a Sharepoint 2007 list called valueList via web service and find the most recent version of each list item, so given this list:

ID | Value
----------
1  | 101
2  | 305
3  | 102
4  | 101
5  | 305
6  | 101

I want my query to return:

ID | Value
----------
3  | 102
5  | 305
6  | 101

In Access SQL, I would do this with:

select v1.* from valueList v1
inner join 
(select value, max(id) as maxID from valueList group by value) v2
on v1.id=v2.maxID

So far it seems like the only way I can do this in C#/CAML/Sharepoint web service is to run a CAML query to group my list items by value and order them by ID, dump the query to a DataTable, then go through every row of the DataTable, picking the last row for each value. Code as follows:

//dataHandler constructor initializes Web Services Lists() object
dataHandler dh = new dataHandler();

string[] fields = { "ID", "value"};
string query =
           "<GroupBy collapse='true'>" +
              "<FieldRef Name='value' />" +
           "</GroupBy>" +
           "<OrderBy>" +
              "<FieldRef Name='ID' />" +
           "</OrderBy>";

DataTable listTable = dh.listToDataTable("valueList", fields, query);

//copy listTable structure to blank resultTable
DataTable resultTable = listTable.Clone();

//initialize value    
int value = (int)Convert.ToDouble(listTable.Rows[0][1]);

for (int ctr = 0; ctr < listTable.Rows.Count; ctr++)
{

    //check to see if we've gone onto a new 'value', if so get previous row
    if (value != (int)Convert.ToDouble(listTable.Rows[ctr][1]) )
    {
        resultTable.ImportRow(listTable.Rows[ctr - 1]);
        value = (int)Convert.ToDouble(listTable.Rows[ctr][1]);
    }

    //get the last row
    if (ctr == listTable.Rows.Count - 1)
    {
        resultTable.ImportRow(listTable.Rows[ctr]);
    }

}

The (int)Convert.ToDouble is because the field is stored as a string in the DataTable, e.g. 1234 gets stored as "1234.0000".

This seems unnecessarily complicated; is there a more direct way to accomplish what I'm after? I'm not required to use DataTables or CAML, if there's something easier.

share|improve this question
    
Have you thought about creating a view in your Sharepoint list then get the datatable for the view? –  Emmie Gabrielle Lewis May 26 '12 at 23:07
    
@EmmieGabrielleLewis: From what I've read about creating a view, the most I can do is a group-by on the value field; I would still have to run a For loop through the datatable to get the highest ID for each value. Or am I missing something? –  sigil May 29 '12 at 14:47
1  
Have you thought about using linq? –  Emmie Gabrielle Lewis May 30 '12 at 10:23
    
@EmmieGabrielleLewis: I haven't used LINQ before, but it looks like a good solution for this problem. Thanks for the suggestion--I'll look into it. –  sigil May 30 '12 at 18:01

2 Answers 2

up vote 1 down vote

It looks like you're doing your own versioning on the list? If you just used SharePoint versioning then it would be easy to get the latest version. :)

Anyways, CAML is pretty limited and in 2007 there are no joins (in 2010 you have joins). Unless you do some sort of pre-processing on the list.. I don't think this is possible.

share|improve this answer
    
This list is populated by a workflow: each time a certain event happens to an item in another list, a new entry gets added to this list, populating value with the ID number from the other list. Because the event could happen to the same listItem more than once, the same value will show up several times and I need to record that an action has been taken on the most recently added copy of value. Would versioning help for this? –  sigil May 29 '12 at 14:54
    
So could you enable versioning on your "Value List" and update the corresponding list item? That way it is 1:1 instead of 1:*. It would be recorded in the version history of the list item. –  Kit Menke May 29 '12 at 16:15
up vote 0 down vote

This link is useful for me :)

DataTable dtList = list.Items.GetDataTable();
var grouped = from row in dtList.AsEnumerable()
          group row by row["Value"] into valueGroup
          select new { Value = valueGroup.Key, ID = valueGroup.Max(id => id["ID"]) };
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.