How to do double orderby in CAML Query?

Question

I am using camel query in visual studio c# to get items from a list from sharepoint 2010. The list items has two fields that I want to use in the caml query. One is "Section" and the other is "Order By". The query needs to order the items in a certain way. First it needs to sort it by Section (ascending=true), and then for each a secondary sorting by Order By (ascending = true).

For example the result would be like this:

<item> <Section> <Order By>
item1  A 1
item2  A 3
item3  B 1
item4  B 2
item5  C 5
item6  C 6

So far I have this:

        SPQuery query = new SPQuery();
        query.Query = "<Query><OrderBy><FieldRef Name='" + Root_List.Fields.GetField(SECTION_COLUMN).InternalName + "' Ascending='True'/></OrderBy></Query>";
        item_collection = Root_List.GetItems(query);

But how do I apply the secondary orderby?

Note: Section is a string field and order by is a number field.

Emendate

perhaps this can help: u2u.be/Tools/wincamlquerybuilder/CamlQueryBuilder.aspx – Marsala

Answer 1

Per this page on MSDN, you can include more than one FieldRef inside the OrderBy element:

http://msdn.microsoft.com/en-us/library/ms467378.aspx

The example given is:

<OrderBy>
  <FieldRef Name="Newcomers"/>
  <FieldRef Name="Years" Ascending="FALSE"/>
  <FieldRef Name="Location"/>
</OrderBy>

Kiernan

Answer 2

I use CAML Builder, it makes life much easier especially when you have difficult queries, you can get it from here:

http://www.u2u.be/res/tools/camlquerybuilder.aspx

the 2007 edition works fine with 2010, I use it daily

Erinaceous

Answer 3

Try to create calculated column that concatenates together the columns you want to ordery by. Then order your list by the new calculated column.

http://msdn.microsoft.com/en-us/library/bb862071(v=office.14).aspx

Campania

Answer 4

in my case at first it seems the above answer is not working . but the real problem was because I later convert them to DataTable and use the following script to sort them and the type of CategoryPosition was not int and was default with string types which would place 10 after 1.

            DataView dv = new DataView(maindt);
            dv.Sort = "Category,CategoryPosition";
            maindt = dv.ToTable();

so watch this kind of things out and caml sorting on multiple sorting is working fine like Steve Mannina answer

Tennyson