Unable to only select First without specifying order By

[xavidram]

In the NReco.GraphQL documentation, the template string looks as follows for MSSQL:

SELECT @columns FROM @table @where[ WHERE {0}] @orderby[ ORDER BY {0}]  @recordcount[LIMIT {0}] @recordoffset[OFFSET {0}]"

From my understanding, the Limit keyword does not exist in T-SQL, but rather it exists in MySQL. And if you look at the MySQL Select Template string on the documentation, it uses FETCH rather than Limit which seems is just a typo. Correct me if I am wrong.

I ran across an issue when using the NReco.GraphQL library, and wanting to only query the first 10 records. The Syntax you have in the Select Template works, but only if I assign a ORDER BY clause. When I query the first: 10 in the graphql string, when the select query is run, my profiler displayed the select query similar to the following:

SELECT Name, Date, Number FROM Table FETCH 10

Which would not work because FETCH as well as OFFSET have to follow the ORDER BY clause. I temporarily solved the issue by replacing @recordcount[FETCH {0}] from the back to the front as @recordcount[TOP {0}] but I realize that might not be the best solution. Is there a way to have a fetch query without having to specify the Order By clause?

[VitaliyMF]

This is definitely a mistake in the NReco.Graphql online documentation (it is already updated). Correct SELECT template for SQL server should be:

dbCmdBuilder.SelectTemplate = "SELECT @columns FROM @table @where[ WHERE {0}] @orderby[ ORDER BY {0}; ORDER BY (SELECT NULL)] @recordoffset[OFFSET {0} ROWS;OFFSET 0 ROWS] @recordcount[FETCH NEXT {0} ROWS ONLY]";

or, if you want to use TOP syntax only:

dbCmdBuilder.SelectTemplate = "SELECT @recordtop[TOP {0}] @columns FROM @table @where[ WHERE {0}] @orderby[ ORDER BY {0}]";
dbAdapter.ApplyOffset = true; // skip first N rows by cursor move