Paperbag Writer Paperbag Writer -5 years ago 85
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.


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 Source

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

  FROM (
        SELECT *,
               NTILE(10) OVER (ORDER BY order_field) as page_number
        FROM yourTable
       ) T
  WHERE T.page_number = @YourPage
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download