CODEWITHSUNDEEP
sqlsql-server
Ronak Shah
Ronak Shah

Reputation: 1549

SQL Server 2012 - Pagination without Order Clause

I found best approach for pagination in SQL Server 2012 is OFFSET FETCH Clause

SELECT First Name + ' ' + Last Name 
FROM Employees 
ORDER BY First Name OFFSET 10 ROWS;

It works fine.

Now in my case, my software generate SQL Server table dynamically and I just know table name.

I don't know columns name in dynamically generated table.

Now in this case how this pagination works ? because I don't know which column should be used with ORDER BY clause with OFFSET Fetch clause..

any alternative solution?

Thanks

Upvotes: 2

Views: 1050

Answers (2)

Mehdi Dehghani
Mehdi Dehghani

Reputation: 11601

As @Tanner mentioned in his answer, in order to use his answer you have to put identity column as the first column of your query, if you can do that, use his answer, but if you can not, use (SELECT NULL) instead of 1, e.g:

SELECT First Name + ' ' + Last Name 
FROM Employees 
ORDER BY (SELECT NULL) OFFSET 10 ROWS;

Upvotes: 0

Tanner
Tanner

Reputation: 22743

You can order by column index if that helps: 

SELECT First Name + ' ' + Last Name 
FROM Employees 
ORDER BY 1 OFFSET 10 ROWS;

This will order by the first column. If the ordering column is stored on the UI (controlled by users), then you need to store the column index on the UI and pass that to SQL Server to use in the ordering.

If you simply want to order by the order the records are added to the table, you will need a primary key, identity column. Make sure that is the first column on the table and use ORDER BY 1.

Upvotes: 3

Related Questions