I know that this query works fine

SPQuery query = new SPQuery();
query.Query = "<OrderBy><FieldRef Name='ItemChildCount' Ascending='FALSE' /></OrderBy>"; 

But my SPListItemCollection doesn't display what I'm expecting as a result.

Actual Dispaly in C#             Actual Display in CAML Query Builder

ID | Title | ItemChildCount|     ID | Title | ItemChildCount |
---|-------|---------------|     ---|-------|----------------|
11 | Abc   |    11;#5      |     11 | Abc   |       5        |
---|-------|---------------|     ---|-------|----------------|
22 | BBB   |    22;#9      |     22 | BBB   |       9        |
---|-------|---------------|     ---|-------|----------------|
24 | Cbc   |    24;#7      |     24 | Cbc   |       7        |

I wonder why in U2U CAML Query Builder, the sort by ItemChildCount doesn't work. I tried to sort the items by ItemChildCount yet it sorts the ID instead.

But, when I check on the result on C# code immediate window using SPListItem["ItemChildCount"] it displays the concatenated ID and the ItemChildCount resulting to something like this 11;#5.

How will I create a query where I can do some string manipulations on the CAML query. Something like this

Select * from ListCollection Order by ItemChildCount.Replace("11;#","")

If ever this is possible.

  • it looks like you use the reverse lookup column ? in all case, can you show us the definition of your column ?
    – Steve B
    Commented Jun 28, 2011 at 11:43
  • Please do not crosspost questions between sites. We should choose one question to keep and delete the other. See meta.stackexchange.com/questions/64068/…
    – Kit Menke
    Commented Jun 29, 2011 at 14:02
  • @Kit Menke: Sorry my bad, I didn't know that it is against the rule :). I was just desperate to find the solution that I'm looking for. Already deleted my other post on stackoverflow. Thanks for the heads up :)
    – Carls Jr.
    Commented Jun 30, 2011 at 1:56

2 Answers 2


I hate to say it, but I'm afraid I don't think this is possible using CAML. Even joining the list to itself and using a GroupBy element wouldn't help because GroupBy isn't equivalent to its SQL counterpart.

My suggestion would be to:

  • Query the Item ID and ItemChildCount from the list (unordered)
  • Create a dictionary keyed by ItemChildCount (stripped), with the value being an array of item IDs that have that ItemChildCount
  • Populate the dictionary with the queried data
  • Work through the dictionary's keys in descending order, querying the Items in batches using the In CAML operator

Example of batch query:

<Where><In><FieldRef Name="ID" /><Values><Value Type="Number">1</Value><Value Type="Number">3</Value></Values></In></Where>

P.S. Trying to turn SQL into C# and CAML makes two very disconnected parts of my brain try to work together in a particularly nauseating way. Bleurgh.


try this :

 var field = ItemChildCount.Fields.GetFieldByInternalName("ItemChildCount");     
 var actualCount = field.GetValueAsHtml(ItemChildCount[field.Id]);

  • 1
    Unfortunately that won't help with ordering the query results. :/
    – Stu Pegg
    Commented Jun 28, 2011 at 12:49
  • The real problem I have is sorting the ItemChildCount. Please refer to my previous issue sharepoint.stackexchange.com/questions/15164/…
    – Carls Jr.
    Commented Jun 29, 2011 at 1:29
  • depending on the number of items you want to retrieve, you can move to a coded orderby using a code similar to the one I provided. But this will be at performance cost, so don't use this if you have a large number of items (or cache the result at least). Don't know if it can be achieved using a pure caml query...
    – Steve B
    Commented Jun 29, 2011 at 7:15
  • Yep I also doubt that this can be done using pure caml query. I'm also looking forward on solving this issue using camlex.net (camlex.codeplex.com). The list I'm working on right now is definitely growing fast because it is a discussion list for the intranet so optimization is also part of my concern :)
    – Carls Jr.
    Commented Jun 30, 2011 at 1:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.