How to Split the Results of a Select Query into Two Equal Halfs

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

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

How do I split a string into 2 parts based on a delimiter?

Here is a simple function that could be used to split string in DB:

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

it will return all the values by splitting with space.

Split Result from SQL Query

If you are using oracle database this is very simple.

you can use ROWNUM keyword to achive this task in following manner..

select * from 
( 
     select tb.*,rownum t_count from table_name tb
) ss
where ss.t_count >= @min_value and ss.t_count <= @max_value

where @min_value and @max_value is the range for what you want to get data...
actually it will work on concept of inner query and temprory tables.

that's why it will give good performance over fatching all the data in one shot.

---

Related Topics