Is there a way to split the results of a select query into two equal halfs?

Question

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

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.

Answer 1

You could use these two queries:

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY TheID) AS rn FROM TheTable
) T1
WHERE rn % 2 = 0

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY TheID) AS rn FROM TheTable
) T1
WHERE rn % 2 = 1

Answer 2

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

Answer 3

try this:

DECLARE @CountOf int,@Top int,@Bottom int
SELECT @CountOf=COUNT(*) FROM YourTable
SET @Top=@CountOf/2
SET @Bottom=@CountOf-@Top
SELECT TOP (@Top) * FROM YourTable ORDER BY 1 asc --assumes column 1 is your PK
SELECT TOP (@Bottom) * FROM YourTable ORDER BY 1 desc --assumes column 1 is your PK

Answer 4

If this is SQL Server 2000, then I'd be inclined to find the PK of the middle value like so:

Declare @MiddleId int

Set @MiddleId = (
                Select TOP 1 PK
                From (
                        Select TOP 50 PERCENT PK
                        From Table
                        Order By TheId ASC
                        )
                Order By TheId DESC
                )

Select ...
From Table
Where TheId <= @MiddleId

Select ..
From Table
Where TheId > @MiddleId

With SQL Server 2005, I'd be inclined to do the same but you can use a CTE

;With NumProjects As
    (
    Select Id, ROW_NUMBER() OVER (ORDER BY TheId ASC ) As Num
    From Table
    )
Select @MiddleId = Id
From Table
Where Num = CEILING( (Select Count(*) From Table) / 2 )