I need a solution for a select query in Sql Server 2005.
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:
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
SELECT TOP 50 PERCENT * FROM TheTable ORDER BY TheID desc
SQL Server 2005 and similar:
select *, ntile(2) over(order by theid) as tile_nr from thetable
ntile(n) allocates the output into n segments, each of the same size (give or take rounding when the number of rows isn't divisible by n). So this produces the output:
1 | value1 | 1 2 | value2 | 1 3 | value3 | 1 4 | value4 | 2 5 | value5 | 2
If you just want the top or bottom half, you need to put this into a subquery, e.g.:
select theid, thevalue from ( select theid, thevalue, ntile(2) over(order by theid) as tile_nr from thetable ) x where x.tile_nr = 1
will return the top half, and similarly use
x.tile_nr = 2 for the bottom half