Sabir Khan Sabir Khan - 1 year ago 60
SQL Question

Is it possible to get partitioned data using SQL?

I have a RDBMS table with a column

type and values are not sequential. I have a java program where I want each thread to get data as per
i.e. I want a pair of column values like after doing
on result,

Column_Value at Row 0 , Column_Value at Row `PARTITION_SIZE`

Column_Value at Row `PARTITION_SIZE+1` , Column_Value at Row `2*PARTITION_SIZE`

Column_Value at Row `2*PARTITION_SIZE+1` , Column_Value at Row `3*PARTITION_SIZE`

Eventually, I will pass above value ranges in a SELECT query's
clause to get divided data for each thread.

Currently, I am able to do this partitioning via Java by putting all values in a
( after getting all values from DB ) and then getting values at those specific indices -
..etc but problem there is that
might have millions of records and is not advisable to store in memory.

So I was wondering if its possible to write such a query using SQL itself which would return me those ranges like below?

row-1 -> minId , maxId

row-2 -> minId , maxId


Database is DB2.

For example,

For table column values
,result of SQL query for a partition size =2 should be
{1,2},{3,4} ,{5,7},{9,11},{12,20},{30}

Answer Source

In my eyes the mod() function would solve your problem and you could choose a dynamic number of partitions with it.

WITH numbered_rows_temp as ( 
SELECT rownumber() over () as rownum,
  FROM table
  ORDER BY col1)

SELECT * FROM numbered_rows_temp
  WHERE mod(rownum, <numberofpartitions>) = 0  

Fill in the appropriate and change the result from 0 to - 1 in your queries.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download