Maintain SQL sort state throughout next query?

Question

I saw this post but didn't understand if this was the same issue.

My database can be sorted on each column. Once the db is sorted, how do I maintain that order after my next query?

For example:
Order db by age:
sprintf(sql, "select * from Customer_Table ORDER BY age_ID LIMIT 100 OFFSET %s;", offset);

then the user looks at the next 100 customers

sprintf(sql, "select * from Customer_Table LIMIT 100 OFFSET %s;", offset); //next offset

The returned data is no longer ordered by age. How would I keep the last sort throughout the next query?

Do I have to pass in a variable to maintain state or does the database have some function for recalling it's last state?

Answer 1

You have to specify the ORDER BY clause at each request.

The way this is optimized by your database system doesn't concern your client code.

Answer 2

SQL databases do not guarantee any sorting, you have to specify it on each and every select call.

Answer 3

Result sets generated by the Database engine are forgotten as soon as they're fulfilled. Everytime you query the database you have to provide your sort order.

Answer 4

Other than selecting the records into a temporary table, then selecting from that table, you would have to specify the ORDER BY in each query. The server does not retain any kind of state in regard to the previous query. Why is it a problem to re-specify the ORDER BY clause in subsequent queries?

Whether you use a temporary table or re-specify the entire query is a matter of how your application is structured.