Mysql – Set in theSQL a predefined order for SELECT queries without using the ORDER BY clause

MySQLodbcorder-by

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.

Best Answer

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

Related Question