MAX query using CAML

I want to select a sharepoint list item which has the Maximum value for a particular column. How can I do this using CAML queries?

<Query>
    <OrderBy>
            <FieldRef Name="particularcolumn" Ascending="FALSE" />
    </OrderBy>
</Query>

How to select MAX field in CAML query, As a workaround, we can retrieve the items you want with CAML query plus with Server Object Model or Client Object Model and save in another  MAX query using CAML. Ask Question Asked 11 years, 2 months ago. Active 1 year, The following CAML query would return the maximum value for a given column:

The following CAML query would return the maximum value for a given column:

var maxValue;

try
{
    using (SPSite objSite = new SPSite(sSiteUrl))
    {
        using (SPWeb objWeb = objSite.OpenWeb())
        {
            SPList objList = objWeb.Lists[sListName];

            SPQuery objQuery = new SPQuery();
            objQuery.Query = "<OrderBy><FieldRef Name='ColumnName' Ascending='False' /></OrderBy><RowLimit>1</RowLimit>";
            objQuery.Folder = objList.RootFolder;

            // Execute the query against the list
            SPListItemCollection colItems = objList.GetItems(objQuery);

            if (colItems.Count > 0)
            {
                maxValue = (<Insert Appropriate Cast>) colItems[0];
            }
        }
    }
}
catch (Exception ex)
{
    ...
}

return maxValue;

select max value records group by field - MSDN, Look at the <Order> part. I don't have a CAML Query ready, but essentially you <​Order> by ID either ascending or descending and pick the first element. values - MAX query using CAML caml query with 2 and conditions (5) Building on this answer I gave to a related question , I would suggest the following query:

This can be done ordering by this field in descending way and taking the first element of the collection returned.

How to get Max(ID) or Min(ID using CAML, I am running a sharepoint caml query where I want to check that a field on an item is I found that when performing a query with 780 OR elements I got an error  By adding LookupId="TRUE" to the <FieldRef /> and using <UserID /> as the value, the query will filter based on the current user. You can also pass the ID of a specific user in place of <UserID /> (e.g. <Value Type="Integer">283</Value>) if you don’t want to filter by the current user.

Using the client object model, this is how I'm retrieving the max ID from a list.

using (ClientContext clientContext = new ClientContext("https://sharepointed.com"))
    {
        clientContext.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

        oWeb = clientContext.Web;

        List myList= oWeb.Lists.GetByTitle("MyList");

        CamlQuery camlQuery = new CamlQuery();
        camlQuery.ViewXml = ("<View><Query> <OrderBy> <FieldRef Name='ID' Ascending='False' /> </OrderBy> </Query> <RowLimit>1</RowLimit> </View>");
        Microsoft.SharePoint.Client.ListItemCollection listItems = myList.GetItems(camlQuery);
        clientContext.Load(listItems);
        clientContext.ExecuteQuery();
     }

CAML Query Maximum Size, The first thought to it was get all the items in list loop through and keep on adding for Aggregation using CAML query 3, Maximum, MAX. You can pass an undefined CamlQuery object to return all items from the list, or use the ViewXml property to define a CAML query and return items that meet specific criteria. The following example displays the ID, as well as the Title and Body column values, of the first 100 items in the Announcements list,

<script type="text/javascript" charset="utf8" src="/jquery-3.2.1.min.js"></script>
<script type="text/javascript" charset="utf8" src="/jquery.SPServices-2014.02.min.js"></script>

<script type="text/javascript">

    function getLastItemId() {
    var userId = _spPageContextInfo.userId;
    var caml = "<View><Query><Where>"
        + "<Eq><FieldRef Name='Author' LookupId='TRUE' /><Value Type='Integer'>" 
        + userId + "</Value></Eq></Where>" 
        + "<OrderBy><FieldRef Name='Created' Ascending='False' /></OrderBy>" 
        + "</Query><RowLimit>1</RowLimit></View>";
    var ctx = SP.ClientContext.get_current()
    var web = ctx.get_web()
//ENTER YOUR LIST NAME BELOW
    var list = web.get_lists().getByTitle("YOUR LIST NAME")
    var query = new SP.CamlQuery();
    query.set_viewXml(caml);
    var items = list.getItems(query);
    ctx.load(items)
    ctx.executeQueryAsync(function() {
// success actions
        var count = items.get_count();
        //should only be 1
        if (count > 1)  {
           throw "Something is wrong. Should only be one latest list item / doc";
        }

        var enumerator = items.getEnumerator();
        enumerator.moveNext();
        var item = enumerator.get_current();
        var id = item.get_id();
// do something with your result!!!!
        alert(id);
}, function() { 
        //failure handling comes here
        alert("failed"); 
});
}
getLastItemId();

</script>
<button id="button1" type="button" onclick="getLastItemId();">click to get last ID</button>

I managed to get the following working I used a script web part and added the following to the web part. When you click the button an alert will pop up with the highest item ID number.

Aggregation using CAML query – Code@Work, In SharePoint the best approach to get the data from a SharePoint list is to query those using either Server Object Model or Client Object Model. The format of the Where clause is a structured XML tree with a mixture of comparison operators, simple arithmetic operators, field (column) references, constant values, and predefined Collaborative Application Markup Language (CAML) constants. Fields referenced in a Where element do not have to be fields of the primary list that is being queried.

SharePoint 2013 CAML Query For Item ID With jQuery, Get all items from 'financial documents' list using 'Query List' action and store 'ID' field in 'Coolection variable. (don't apply filter conditions in  Get Max Date Using CAML Query From alist The brute force approach is to create two queries that will retrieve the list content sorted by date asc and desc. I know that this sucks but at least you can move on with you project and refine the query later on. If only it was possible to retrieve top 1 then it might even work in production.

Solved: CAML Query and Conditions, Specifies the maximum number of rows to get with the CAML query. Accepted Values: Deselected - Specifies to get 10,000 maximum from the CAML query.

Configure Lookup for SharePoint > CAML Query Name/Value , When you perform a CAML query, the query results in an SQL query clause with the above, your WHERE should match a maximum of 5000 

Comments
  • Don't forget to set RowLimit on the query.
  • What is the benefits of settings objQuery.Folder = objList.RootFolder; ?