Set in mySQL a predefined order for SELECT queries without using the ORDER BY clause

Question

I have a MySQL database and users access it through an old legacy software (OLS) the company bought years ago. OLS connects to MySQL through MySQL ODBC driver. There is no way to change OLS in recent future. The OLS is really slow when browsing records, so the idea is to provide OLS with records in the order users expect them i.e. the last created record, first. I have an auto incremental field, so when I order by desc the records, I get what I need. The problem is that OLS does not allow to manipulate query: the only thing it does is to ask for the whole table (I suppose it internally uses the query "SELECT * FROM xyz;").

I learned here that in SQL there is no "predefined" order if there is no order by clause.

So my question is: how can I tell MySQL "if you get a SELECT statement without ORDER BY clause, then ORDER the table desc using Auto ID field"?

If I can not force MySQL, but I have to act on the ODBC driver, that's is still ok for me.

Oh, by the way, sorry for any error i could have had, is my first question and I tried to stick to the rules.

• Masoud: store ordered data in ur table. this is easy to do if your table does not get updated often.
• tombom: @Masoud There is no ordered data in a relational database unless you specify it with ORDER BY!
• Colin 't Hart: Can you rename the table(s) and replace the table(s) with view(s) that simply do select * from <underlyingtable> order by <column>?
• Giorgio R: @Masoud How can I store ordered data?
• Giorgio R: @Colin'tHart <strike>Yes and no. If users have just to see data, no problem with that. But there are also form that modify data and in that case a view is not a viable solution.</strike> Just learnt that there are updatable view.

Answer 1

Like you already found out, there is no (predefined) order in a result set unless you specify it in the ORDER BY clause.

Your only option would be, to rename the table and create a view on it.

RENAME TABLE your_table TO another_table_name;
CREATE VIEW your_table AS 
SELECT * FROM another_table_name ORDER BY id DESC;

Test of course heavily if you run into problems, but I assume there shouldn't be any problems since the view would even be updatable and insertable like it says in this manual entry:

For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable. To be more specific, a view is not updatable if it contains any of the following:

• Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

• DISTINCT

• GROUP BY

• HAVING

• UNION or UNION ALL

• Subquery in the select list

• Certain joins (see additional join discussion later in this section)

• Reference to nonupdatable view in the FROM clause

• Subquery in the WHERE clause that refers to a table in the FROM clause

• Refers only to literal values (in this case, there is no underlying table to update)

• ALGORITHM = TEMPTABLE (use of a temporary table always makes a view nonupdatable)

• Multiple references to any column of a base table

• jkavalik: There are some performance implications from using views in complex queries so test it well on big enough data.
• ypercubeᵀᴹ: I don't think that even this solution guarantees anything. The ORDER BY in an internal view, when you run SELECT * FROM view_name; may be ignored in some (or future) versions. The order of the result set is not guaranteed to follow the internal ordering.