1

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.

  • store ordered data in ur table. this is easy to do if your table does not get updated often. – Masoud Jul 24 '15 at 8:12
  • @Masoud There is no ordered data in a relational database unless you specify it with ORDER BY! – tombom Jul 24 '15 at 8:33
  • Can you rename the table(s) and replace the table(s) with view(s) that simply do select * from <underlyingtable> order by <column>? – Colin 't Hart Jul 24 '15 at 8:39
  • @Masoud How can I store ordered data? – Giorgio R Jul 24 '15 at 8:39
  • @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. – Giorgio R Jul 24 '15 at 8:42
2

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

  • There are some performance implications from using views in complex queries so test it well on big enough data. – jkavalik Jul 24 '15 at 8:49
  • 1
    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. – ypercubeᵀᴹ Jul 24 '15 at 9:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.