Pagination with Order By - SQL Server

Description

Create an inner query to select from a table. Then, create an outer query to perform the limit on that in order to provide better paging. Then put an 'ORDER BY' option onto the query.

In MySQL etc this is fine because of the limit clause exists, but we are getting strange results in SQL Server when it uses the TOP and the sub queries.

Basically, SQL Server reports it as inaccurate as it has a ORDER BY in the sub query which is where it can't go. An example is (as produced by our application:

I have created a test case for this which runs fine on the internal testing for Hibernate, but I am sure it will fail when you run it on SQL Server. It creates 19 records and does a select on the first ones.

Happy for it to be ourselves, but I can't see why this would produce invalid SQL for SQL Server when we are not changing anything. But I can see how it is valid for MySQL etc as the limit function seems to work fine.

Dialect is SQL2008 but it does the same with 2012.

Environment

None

Activity

Show:
Christian Beikov
February 8, 2017, 3:30 PM

Might be a duplicate? This stuff will be solved in 6.0 since we will rewrite how pagination works.

Assignee

Unassigned

Reporter

Mathew Johnson

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

Priority

Major
Configure