Skip to main content
SharePoint

Return to Answer

Modified answer based on user input
Source Link
Akhoy
Akhoy
  • 2.9k
  • 2
  • 18
  • 28
**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));
});
});
**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));
});
});
Source Link
Akhoy
Akhoy
  • 2.9k
  • 2
  • 18
  • 28

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);
            }
        }