Order by most recent date not working in CAML Query

Question

Hi,

I am trying to pull items from an announcements list and I would like the results sorted by most recent first, but it is coming back as least recent first. Can someone tell me what is wrong with the query? I even tried "Modified" and I get the same result. Thanks.

         SPList oList  = SPContext.Current.Site.OpenWeb("ts/hse").Lists["near misses"];
         
 
           SPQuery spQuery = new SPQuery();
           spQuery.Query = "<Query><OrderBy><FieldRef Name='Created' Type='DateTime' IncludeTimeValue='TRUE'  Ascending='False' /></OrderBy></Query>";
           spQuery.RowLimit = 3;

           SPListItemCollection spListItemCollection = oList.GetItems(spQuery);

---

Sherazad

Answer 1

Sort date is not easy ..

Check if the query is actually right:

https://www.nothingbutsharepoint.com/sites/eusp/pages/understanding-filters-in-sharepoint-list-views.aspx

Or use the CAML Query Builder:

http://www.codeproject.com/Articles/458008/CAML-Query-Builder

Answer 2

Hi,

I did a test in my environment as the capture below. It works properly.

I suggest you try the code in another list and test whether it works.

Feel free to reply with the test result.

Thanks

Patrick Liang

Forum Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact [e-mail]

---

Patrick Liang
TechNet Community Support

Answer 3

So I took the suggestion of downloading U2 caml query helper. The query returns the right data in the caml query helper, but when copied over into Visual Studio 2013 with update 2, the data returned is incorrect. Any other ideas :(

---

Sherazad

Answer 4

Hi,

I did a test in the Visual Studio 2013 Update 2. The result is right.

I suggest you create a new list first and add 3 new items. Then, try the code in the Visual Studio 2013 Update 2.

Feel free to reply with the test result.

Best regards

---

Patrick Liang
TechNet Community Support

Answer 5

Hi,

Thanks for all your input. I had to wind up calliing Microsoft about this. Here are the answers. For the query to work to find featured articles, I had to put the query in a string.concat like so and check for a boolean value of 1 not true:

           
  SPQuery oQuery = new SPQuery();

  oQuery.Query = string.Concat("<OrderBy><FieldRef Name='Created' Ascending='FALSE'></FieldRef></OrderBy>",
                                      "<Where><Eq>",
                                      "<FieldRef Name='Featured'/>",
                                      "<Value Type='Boolean'>1</Value>",
                                      "</Eq></Where>");
           oQuery.RowLimit = 4;

Also, I had a problem with ordering by descending. The fix was that the value of the attribute "Ascending" had to be an ALL UPPERCASE "FALSE" :)

Hopefully, this will save someone else some time in the future.

---

Sherazad