Is there a way to split the results of a select query into two equal halfs?
- by Matthias
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. Example:
I've got a simple table with TheID (PK) and TheValue fields (varchar(10)) and 5 records. Skip the where clause for now.
SELECT TOP 50 PERCENT * FROM TheTable ORDER BY TheID asc
results in the selected id's 1,2,3
SELECT TOP 50 PERCENT * FROM TheTable ORDER BY TheID desc
results in the selected id's 3,4,5
3 is a dup. In real life of course the queries are fairly complicated with a ton of where clauses and subqueries.
