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?

Andomar · Answer 1 · 2017-03-06T22:42:58.450

0

A subquery returns an unordered set. So specifying order by for a subquery is not allowed. Only the outermost query can have an order by.

An exception is if you specify limit N in the same subquery as the order by. The subquery will still return an unordered set, but the order by can be used to determine the top N rows. For example:

SELECT  SUM(col1) as SumOfTop5Col1
FROM    (
        SELECT  col1
        FROM    YourTable
        ORDER BY
                col1 desc
        LIMIT   5
        ) SubQuery

edited Mar 06 '17 at 22:42

answered Mar 06 '17 at 15:27

Andomar

232,371
49
380
404

Thank you, that helped understandig the error much. With the outer query I wanted to make a SUM at the top X (e.g. 5) ordered results: `SELECT SUM("ROWname") AS "Alias" FROM (SELECT * FROM (SELECT "ROWname" FROM TABULARname ORDER BY "ROWname" DESC) LIMIT 5);` If I can add the `ORDER BY` only in the most outer query it will order the 5 (random) results. But I want to order first and then take the 5 top results. How do I manage that? – DoJo Mar 06 '17 at 20:46
You can do that, but the `limit` should be in the same subquery as the `order by`. Updated the answer, hope that helps! – Andomar Mar 06 '17 at 22:44
Sadly I get the same error message: no `ORDER BY` allowed there. As you said it's an inner subquery still that tries to order … – DoJo Mar 07 '17 at 03:49
Are you on MySQL? Check `SELECT VERSION();` – Andomar Mar 07 '17 at 11:05
No, HSQLDB as mentioned by @eliptical-view. That check returns an error. – DoJo Mar 09 '17 at 16:07

score 0 · Answer 2 · answered Mar 06 '17 at 15:29

0

try this

SELECT *
FROM TABULARname
ORDER BY ROWname

answered Mar 06 '17 at 15:29

Judith Palacios

413
2
9

score 0 · Accepted Answer · answered Mar 08 '17 at 05:55

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.

nested ORDER BY in SQL statement in LibreOffice Base

3 Answers3