Alexandr Kadenchuk Alexandr Kadenchuk - 15 days ago 6
SQL Question

offset count(*) / N columns

I do some job in few machines.
Need divide rows into equal parts for each machine.
Query with all conditions performed long time.
Is it possible to do this without additional request count(*), in one query.
Some like this:

select * from some_table
where some_rows = some_values
offset (count(*) / count_machines) * (machine_number - 1)
limit count(*) / count_machines

Answer

What I sometime do to split data into blocks is a simple modulo on some id:

select * from some_table
where some_rows = some_values
and mod(id, BLOCKS) = BLOCK - 1;

Replace BLOCKS with the number of machines and BLOCK with a number n from 1 to BLOCKS to retrieve block n.

(You can even speed this up with a function index on the modulo expression provided the number of machines is static and you need this more often.)