Paperbag Writer Paperbag Writer - 5 months ago 8
SQL Question

Selecting record range dynamically in a SQL Server query

I have a big query to make and then I convert to JSON so I get OutofMemory Exception in JAVA during my conversion. What I'm trying to achieve is split the query dynamically calling it multiple time.

i.e

SELECT ROWS x/10 multiply by (SELECT COUNT(*) FROM myquery) to
y/10 multiply by (SELECT COUNT(*) FROM myquery)
FROM myquery


I'm calling this query from a python script on a http endpoint so I can separate my load as to call it ten times for example replacing x , y by 1to10.

Example Select the first 0/10 to 1/10 records and then 1/10 to 2/10 and then 2/10 to 3/10 ... 9/10 to 10/10.

Answer

You need a field to order your rows call it order_field. And use the analytic function NTILE(10) to create 10 groups

Then pass a variable @YourPage to indicate what page you want

  SELECT *
  FROM (
        SELECT *,
               NTILE(10) OVER (ORDER BY order_field) as page_number
        FROM yourTable
       ) T
  WHERE T.page_number = @YourPage