locked
CAML query order by not working in SPList RRS feed

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



    Thursday, November 21, 2013 11:17 AM

Answers

  • 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

    Thursday, November 21, 2013 1:52 PM
  • 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

    • Marked as answer by tracycai Wednesday, November 27, 2013 4:21 AM
    Thursday, November 21, 2013 2:53 PM

All replies

  • 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

    Thursday, November 21, 2013 1:52 PM
  • 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

    • Marked as answer by tracycai Wednesday, November 27, 2013 4:21 AM
    Thursday, November 21, 2013 2:53 PM