"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?

score 4 · Answer 1 · answered Jul 25 '16 at 00:03

4

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

answered Jul 25 '16 at 00:03

Anthony Damico

score 1 · Answer 2 · answered Dec 05 '17 at 00:32

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

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.

2 Answers2