SQL to split the total number of rows

Question

I'm reading a table from a Java app and now I wanted to split this task across two different apps each processing its own rows.

I tried to use this

Select * from emp where rownum between 0 and (Select count(*) from emp)/2;

I need your suggestion on the best way to read the next half of the table.

I tried

Select * from emp where rownum between ((Select count(*) from emp)/2)+1 and (Select count(*) from emp);

but it didn't give me results ( gave me a empty set of records )

is this oracle or mysql ? mysql does not have rownum.. also define `didnt give me results` — amdixon, Dec 31 '15 at 07:58
You are using oracle 12 c.. this [http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering/26051830#26051830] may help you — Dharani Dharan, Dec 31 '15 at 08:07

score 0 · Answer 1 · answered Dec 31 '15 at 08:07

0

rownum is assigned to the rows after the result was fetched, so you can't use it as you did. Instead you can do something like:

   select *
   from (select rownum r, e.* from emp e)
   where r between ((Select count(*) from emp)/2)+1 and (Select count(*) from emp);

answered Dec 31 '15 at 08:07

Grisha Weintraub

This doesn't guarantee that the same row is not fetched by both apps. The rows must be ordered. – Husqvik Dec 31 '15 at 08:56
That's why I wrote `something like`. OP will decide by himself how exactly he will order the records. – Grisha Weintraub Dec 31 '15 at 09:08

score 0 · Answer 2 · answered Dec 31 '15 at 08:52

You can also interleave the rows

Assuming sequential id:

App 1: Select * from emp where mod(emp_id, 2) = 0;

App 2: Select * from emp where mod(emp_id, 2) = 1;

or

App 1:

WITH emp_ordered AS (Select * from emp order by emp_id)
Select * FROM (Select emp.*, rownum rn from emp_ordered) where mod(rn, 2) = 0;

App 2:

WITH emp_ordered AS (Select * from emp order by emp_id)
Select * FROM (Select emp.*, rownum rn from emp_ordered) where mod(rn, 2) = 1;

2 Answers2