title: Customize TFS Work Item Query
body: <p>I need to customize a Work Item query in a way that does not seem possible using the very limited WIQL.  I have a fairly standard query which needs changing slightly:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;WorkItemQuery Version="1"&gt;
  &lt;TeamFoundationServer&gt;http ://blah-
  blah01:8080/tfs/blahcollection&lt;/TeamFoundationServer&gt;
  &lt;TeamProject&gt;Blah&lt;/TeamProject&gt;&lt;Wiql&gt;
  
  SELECT 
  [Microsoft.VSTS.Common.Priority],[System.AreaPath],
  [Microsoft.VSTS.Common.Severity],[System.CreatedDate],[System.Id],
  [System.WorkItemType],[System.Title],[System.CreatedBy],[System.State],
  [System.AssignedTo],[System.ChangedDate],[BLAH.Category],[System.AreaId] 
  
  FROM WorkItems
  
  WHERE [System.TeamProject] = @project 
  and [System.WorkItemType] &amp;lt;&amp;gt; '' 
  and [System.CreatedDate] &amp;gt;= '2012-01-27T00:00:00.0000000' 
  and [System.CreatedBy] &amp;lt;&amp;gt; 'blah' 
  and not [System.State] contains 'Tested' 
  and not [System.State] contains 'Released' 
  and [System.State] &amp;lt;&amp;gt; 'Developed' 
  and ([System.State] &amp;lt;&amp;gt; 'Closed' 
  and not [System.State] contains 'Resolved') 
  and [System.AssignedTo] in ('blah1', 'blah2', 'blah3') 
  
  ORDER BY 
  
  [System.WorkItemType], 
  [Microsoft.VSTS.Common.Priority], 
  [System.AreaPath], 
  [Microsoft.VSTS.Common.Severity], 
  [System.Id] desc, 
  [System.AssignedTo] 
  
  &lt;/Wiql&gt;&lt;/WorkItemQuery&gt;
  </code></pre>
  
  <p>It should return the exactly same result as it does now, except that within the [System.AreaPath] column, items containing 'Foo' should appear first, items containing 'Bar' should appear next, and then the rest of the column should be ordered as normal.</p>
  
  <p>This is straight forward enough in T-SQL but seemingly impossible using WIQL so I would appreciate any ideas or workarounds.</p>
  
  <p>Something that may be relevant to the workaround is the unfortunate fact that the TFS instance sits on top of SQLExpress rather than full SQL Server (it was originally a proof of concept and is in the process of being replaced, there's nothing I can do about that at the moment but I still have to sort the query out)</p>
  
  <p>Many thanks
  Simon</p>