title: Is there a way to split the results of a select query into two equal halfs?
body: <p>I need a solution for a select query in Sql Server 2005.</p>
  
  <p>I'd like to have a query returning two ResultSets each of which holding exactly half of all records matching a certain criteria. I tried using TOP 50 PERCENT in conjunction with an Order By but if the number of records in the table is odd, one record will show up in both resultsets. I don't want to have any record duplicated over the recordsets. Example:</p>
  
  <p>I've got a simple table with TheID (PK) and TheValue fields (varchar(10)) and 5 records. Skip the where clause for now.</p>
  
  <pre><code>SELECT TOP 50 PERCENT * FROM TheTable ORDER BY TheID asc
  </code></pre>
  
  <p>results in the selected id's 1,2,3</p>
  
  <pre><code>SELECT TOP 50 PERCENT * FROM TheTable ORDER BY TheID desc
  </code></pre>
  
  <p>results in the selected id's 3,4,5</p>
  
  <p>3 is a dup. In real life of course the queries are fairly complicated with a ton of where clauses and subqueries.</p>