“ORDER BY” in subquery - not avaliable in MonetDB?

Question

I found that, when using order-by directly, it is ok.

SELECT t0."D" AS fd,
  SUM(t0."SD") AS top
FROM "mock_table_1" AS t0
GROUP BY t0."D"
ORDER BY top ASC
LIMIT 10

but when using it in a subquery, an syntax error is reported.

SELECT * FROM (
  SELECT t0."D" AS fd,
    SUM(t0."SD") AS top
  FROM "mock_table_1" AS t0
  GROUP BY t0."D"
  ORDER BY top ASC
  LIMIT 10
)

here is the error message.

syntax error, unexpected ORDER, expecting UNION or EXCEPT or INTERSECT or ')' in: "select t0."A" as d0,

So, I wonder if monetdb is designed to be like this, or it is a bug?

Answer 1

that is the expected behavior. offset, limit, and order by are not allowed in subqueries

https://www.monetdb.org/pipermail/users-list/2013-October/006856.html

Answer 2

SQL-conforming DBMSes are not supposed to allow ORDER BY in subqueries, because it contradicts the conceptual model of a relational DBMS. See:

Is order by clause allowed in a subquery

for details. A way around that, however, is to use Window Functions, which MonetDB does support. Specifically, in your subquery, instead of, say,

SELECT c1 FROM t1;

you can

SELECT c1, ROW_NUMBER() OVER () as rownum from t1;

and now you have the relative order of the inner query result available to the outer query.