CAML retrieve only the latest of each value in a column

Question

(Sorry if the title isn't clear I'm not really sure how to phrase it in one question)

Situation: I have a list in sharepoint containing some evaluation of different teams in a company with at least the following fields:

teamname, date of evalution and some fields describing their evaluation

Teams get evaluated multiple times (roughly every 3 months, but this is by no means exact) and as such have many entries in this list

Problem: I want to use CAML to retrieve only the last item corresponding to each of the different teams. is this possible?

Example: given the following table:

+-------+----------+-------+
| team  | created  | score |
+-------+----------+-------+
| red   | 5/7/15   |    10 |
| blue  | 23/6/15  |     9 |
| red   | 26/3/15  |     8 |
| green | 24/3/15  |     8 |
| blue  | 9/1/15   |     6 |
| red   | 10/12/14 |     8 |
| green | 8/12/14  |     9 |
| blue  | 4/12/14  |     5 |
+-------+----------+-------+

I would like te retrieve:

+-------+---------+-------+
| team  | created | score |
+-------+---------+-------+
| red   | 5/7/15  |    10 |
| blue  | 23/6/15 |     9 |
| green | 24/3/15 |     8 |
+-------+---------+-------+

Is this possible using CAML?

Answer 1

CAML Query cannot retrieve distinct rows. You can weed out duplicate values after applying the query.

        DataTable dt=new DataTable();
        dt.Columns.Add("team");
        dt.Columns.Add("created");
        dt.Columns.Add("score");
        StringBuilder output = new StringBuilder();
        using (ClientContext ctx = new ClientContext("http://siteUrl"))
        {
            Web web = ctx.Web;
            List list = web.Lists.GetByTitle("ListTitle");
            var q = new CamlQuery() { ViewXml = "<View><Query><OrderBy><FieldRef Name='Created' Ascending='False' /></OrderBy></Query></View>" };                
            ListItemCollection listOfItems = list.GetItems(q);
            ctx.Load(listOfItems);
            ctx.ExecuteQuery();
            foreach (Microsoft.SharePoint.Client.ListItem l in listOfItems)
            {
                dt.Rows.Add(l["team"].ToString(), l["created"].ToString(), l["score"].ToString());
            }
            var listOfDataRows = dt.AsEnumerable().GroupBy(x => x["team"]).Select(x => x.First()).ToList();
            foreach(DataRow dr in listOfDataRows)
            {
                foreach(string col in dr.ItemArray)
                {
                    output.Append(col);
                    output.Append(";");
                }
                output.Append(Environment.NewLine);
            }
        }

---

Edit: Edited post based on user comment

Assuming you're using SharePoint 2013, you can use REST api to achieve this.

<script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
<script>
function getDataWithCaml(siteurl,listName, caml) {
var endpoint = siteurl+"/_api/web/lists/GetByTitle('"+listName+"')/GetItems";
var requestData = { "query" :
       {"__metadata": 
          {"type": "SP.CamlQuery" }
          , "ViewXml": caml
       }
    };
return $.ajax({
    url: endpoint,
    method: "POST",
    data: JSON.stringify(requestData),
    headers: {
        "X-RequestDigest": $("#__REQUESTDIGEST").val(),
        "Accept": "application/json; odata=verbose",
        "Content-Type": "application/json; odata=verbose"
    }
});}    
</script>

Usage:

$(document).ready(function(){
var caml = "<View><Query><OrderBy><FieldRef Name='Created' Ascending='False' /></OrderBy></Query></View>";
getDataWithCaml("http://siteUrl","ListUrl", caml).done(function(data)
{
var data= data.d.results;  
var result = [];  
var lookup = {};
for (var item, i = 0; item = data[i++];) {
  var team= item.team; //the team column value
  if (!(team in lookup)) {
   lookup[team] = team;
   result.push(item);  
}}
var len = result.length;
console.log(len);
for (i=0; i<len; ++i) {
console.log(result[i].team + ";" + result[i].created + ";" + result[i].score);
}
})
.fail(
function(error){
console.log(JSON.stringify(error));
});
});

Answer 2

You could set the RowLimit property of your SPQuery object.

The tag is in the schema definition of a view (direct child of ) and therefore cannot be nested inside a tag.

Answer 3

You can use CAML Query to a certain extend. You'd have to combine it with LINQ.

First you need to know all of the teamnames

var teamList = SPContext.Current.Web.Lists.TryGetList("TeamList");
var teamItems = teamList.Items;
// Get all the team names in the list, but only 1 of each
var teamnames = teamItems.Cast<SPListItem>().Select(item => (string)item["TeamNameColumn"]).Distinct();

Now that you have the team names, you can now loop through the list of team names and get the items with those names

var results = new Dictionary<string, SPListItemCollection>()
var teamQuery = new SPQuery() 
{
    // This will only return a specific set of fields for each item
    ViewFields = string.Concat("<FieldRef Name='TeamNameColumn'/>",
                               "<FieldRef Name='Created'/>",
                               "<FieldRef Name='ScoreColumn'/>"),
    ViewFieldsOnly = true,
    // We are only finding scores by 1 teamname at a time, 
    // and since we order by descending on Created (see below) we have the
    // latest on top, so we can just take the top row.
    RowLimit = 1
}

foreach (var teamname in teamnames)
{
    teamQuery.Query = string.Format(string.Concat(
                          "<Where>",
                              "<Eq>",
                                  "<FieldRef Name='TeamNameColumn'/>",
                                  "<Value Type='Text'>{0}</Value>",
                              "</Eq>",
                          "</Where>",
                          "<OrderBy>",
                              "<FieldRef Name='Created' Ascending='FALSE'/>",
                          "</OrderBy>"), teamname);
    var latestScore = teamList.GetItems(teamQuery);
    results.Add(teamname, latestScore);
}

Now you should have the latest score for all teams in the list and should be able to write them out

foreach (var result in results)
{
    // We select index 0 because it's a collection
    var spItem = result.Value[0];
    var output = string.Format("{0} {1} {2}", spItem["TeamNameColumn"].ToString(),spItem["TeamScore"].ToString(),spItem["Created"].ToString());
    // Write the output to somewhere...
}