CAML query order by not working in SPList

Question

I am using the following code display items in list but it is not sorting them. No matter what I write in order by clause, it always brings the same results.

SPListItemCollection items = null;
using (SPSite site = new SPSite(SPContext.Current.Site.Url))
{
     using (SPWeb web = site.OpenWeb("mysite"))
     {
          SPList list = web.Lists["Pages"];
          SPQuery query = new SPQuery();
          query.Query = "<ViewFields><FieldRef Name='Title' /><FieldRef Name='Created' /><FieldRef Name='LinkFilenameNoMenu' /></ViewFields><OrderBy><FieldRef Name='Created' Ascending='TRUE' /></OrderBy><QueryOptions><RowLimit>5</RowLimit></QueryOptions>";
          items = list.GetItems(query);
          foreach (SPListItem lst in list.Items)
          {
               label1.Text += "||"+ lst["Title"].ToString() ;
          }
     }
}

Answer 1

I took a look at your code and was supprised to see that you aren't doing anything wrong:
These are the results I got myself on a document library:

FieldName:Created FieldValue:9/26/2013 3:55:46 PM
FieldName:Created FieldValue:10/16/2013 11:16:35 AM
FieldName:Created FieldValue:10/16/2013 11:16:45 AM

As you can see they are ordered, just like you requested :-)

No not really.. You see my test is faulty, it seems to order by month day and then year so 10/5/2001 would end up behind 1/1/2013. 

You can fix this by ordering by the "Created_x0020_Date" Field.
Since this field seem to be displayed in a year-month-day order

FieldName:Created_x0020_Date FieldValue:2013-10-16 11:16:46

        public override void FeatureActivated(SPFeatureReceiverProperties properties)
        {
            SPListItemCollection items = null;
            SPWeb web = (SPWeb)properties.Feature.Parent;

            SPList list = web.Lists.TryGetList("Documents");
            SPQuery query = new SPQuery();
            query.Query = "<ViewFields><FieldRef Name='Title' /><FieldRef Name='Created_x0020_Date' /><FieldRef Name='LinkFilenameNoMenu' /></ViewFields><OrderBy><FieldRef Name='Created_x0020_Date' Ascending='TRUE' /></OrderBy><QueryOptions><RowLimit>5</RowLimit></QueryOptions>";
            items = list.GetItems(query);
            foreach (SPListItem lst in list.Items)
            {
                foreach(SPField fld in lst.Fields)
                {
                    if (lst[fld.InternalName] != null)
                    System.Diagnostics.Debug.WriteLine("FieldName:" + fld.InternalName + " FieldValue:" + lst[fld.InternalName].ToString());
                }
            }
        }

---

Kind regards, Rainier van Slingerlandt | www.justanothertechnologyguy.com

Answer 2

Have you seen this post?

http://social.msdn.microsoft.com/Forums/sharepoint/en-US/e48e138b-e2d0-4aa4-8acc-b3b0a43e0cf1/caml-orderby-question?forum=sharepointdevelopmentlegacy

Basically something was being cached. Moving the web part to another part of the page seemed to reset it.

---

Doug Hemminger http://www.sharepointdoug.com