nested ORDER BY in SQL statement in LibreOffice Base

Question

In LibreOffice Base I want to, as reduced example, query the following:

SELECT *
FROM
(
    SELECT "ROWname"
    FROM "TABULARname"
    ORDER BY "ROWname"
);

The Error statement is:

Cannot be in ORDER BY clause in statement [SELECT*FROM(SELECT"ROWname" FROM "TABULARname" ORDER BY "ROWname")]

Without the outer query {SELECT * FROM (…);} it works. So what's the reason it can't be in an ORDER BY clause in that statement?

Answer 1

This is a limitation of HSQLDB 1.8 (the default Embedded DB in LibreOffice). It makes the subquery, i.e. the stuff inside the outer () as a view and not a query, and it disallows ordering in it. :-(

MariaDB and MySQL don't have this limitation.

Take the order clause out of the subquery and move it to your outer query if you can.