LS not reading jdbc input for large dataset. Order By Clause is a Issue in Microsoft SQL

[rkhapre (RK)]

Hi All

I know this question has been asked earlier as well, but this issue is still there.
But there was no clear statement on the solution, so i am asking again.
input : jdbc-input-plugin
jdbc driver: MSSQL
Underneath jdbc input plugin uses this format/template

SELECT count(*) AS count FROM 
(
select .......
...........
) 
AS t1 LIMIT 1

When i use Order by query, it just utilizes the above template

SELECT count(*) AS count FROM 
(
select .......
...........
order by a.id asc
) 
AS t1 LIMIT 1

with this i get this error

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Now i cannot go and change the jdbc-input plugin template and get rid of that count. The count is also important because, i need to enable pagination

I do not need solution without pagination because i have millions on record, so pagination is necessary and to do that i need to do order by ascending.

Is there any other way to do ascending order?

But the above order by query works in other Databases, only MSSQL server is a problem

Thanks