Issue Details (XML | Word | Printable)

Key: NUCRDBMS-559
Type: Improvement Improvement
Status: Open Open
Priority: Minor Minor
Assignee: Unassigned
Reporter: Andy Jefferson
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

SQLServer : Support ROW_NUMBER() OVER() for range restriction on SELECTs

Created: 12/Sep/11 08:23 PM   Updated: 30/Dec/13 09:45 AM
Component/s: Queries
Affects Version/s: None
Fix Version/s: None


 Description  « Hide

Sort Order: Ascending order - Click to sort in descending order
Alexander Bieber added a comment - 10/Oct/11 11:06 AM
This comment shows how the generated SQL could look like.

Using the ROW_NUMBER() and OVER() statements requires either an order-clause or a partition-clause.
For this use-case the order-clause should be defined. I'd suggest to order by primary-key, if there is no ordering present in the JDOQL/JPQL and otherwise to use the user-defined ordering.

Note, that the ROW_NUMBER() method is 1-based, so to express the JDOQL (fromIncl, toExcl) one could use

WHERE RowNumber > fromIncl AND RowNumber <= toExcl

or

-- SQLServer between is inclusive
BETWEEN fromIncl+1 AND toExcl


The actual query would use an inline-view either expressed using the WITH keyword or as sub-query for the from-expression.

-- Example using WITH:

WITH OrderedQuery AS
(
  SELECT t.Field1, t.Field2, t.Field3,
  ROW_NUMBER() OVER (ORDER BY t.IdField) AS 'RowNumber'
  FROM "dbo"."tblFirmenAnsprechpartner" t
)
SELECT *
FROM OrderedQuery
WHERE RowNumber > fromIncl AND RowNumber <= toExcl

-- ###################################

-- Example using from-sub-query

SELECT * FROM
(
  SELECT SELECT t.Field1, t.Field2, t.Field3,
  ROW_NUMBER() OVER (ORDER BY t.IdField) AS 'RowNumber'
  FROM "dbo"."tblFirmenAnsprechpartner" t
)
WHERE RowNumber > fromIncl AND RowNumber <= toExcl


SELECT * FROM
(
  SELECT SELECT t.Field1, t.Field2, t.Field3,
  ROW_NUMBER() OVER (ORDER BY t.UserOrderField1 t.UserOrderField2 DESC) AS 'RowNumber'
  FROM "dbo"."tblFirmenAnsprechpartner" t
)
WHERE RowNumber BETWEEN fromIncl+1 AND toExcl


Unfortunately I was not able to find out which SQLServer version support this.
I've successfully tested with SQLServer 2008 and found some posts saying it will work at least down to SQLServer 2000.

Hope this was useful.

Andy Jefferson added a comment - 30/Dec/13 09:45 AM
Note that this is for SQLServer prior to 2012. With SQLServer 2012 there is support for SQL 2008 standard OFFSET/FETCH which is added by NUCRDBMS-733