PostgreSQL Question posted in
The official documentation can be found here.

Is using ORDER BY and LIMIT in a PostgreSQL subquery defined behaviour?

It is well known that using ORDER BY in a subquery in PostgreSQL is not meaningful, and the DBMS will only respect the outer most ORDER BY.

However, if we include LIMIT (alongside ORDER BY) in the sub query, is this still the case?

Tags:

2

Answers


  1. 0

    Using LIMIT in a subquery will affect the intermediate result set generated by that subquery. Consider the following two queries:

    SELECT *
FROM
(
    SELECT *
    FROM yourTable
    ORDER BY col1
) t
ORDER BY col2;

    In this query, the inner ORDER BY will (or maybe will not) execute, but since there is no LIMIT, the ordering of that intermediate result has no bearing on the ordering of the final result. The order of the final result will only depend on the outer ORDER BY clause.

    However, in this query:

    SELECT *
FROM
(
    SELECT *
    FROM yourTable
    ORDER BY col1
    LIMIT 10        -- only keep first 10 records
) t
ORDER BY col2;

    The subquery aliased as t would only return 10 records. Again, the final ordering would only depend on the outer ORDER BY clause, but there would only be 10 records in the result set, i.e. that result set might be different than the first query.

    Login or Signup to reply.
  2. 0

    From the docs:

    https://www.postgresql.org/docs/current/sql-select.html#SQL-LIMIT

    The query planner takes LIMIT into account when generating a query plan, so you are very likely to get different plans (yielding different row orders) depending on what you use for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.

    It is even possible for repeated executions of the same LIMIT query to return different subsets of the rows of a table, if there is not an ORDER BY to enforce selection of a deterministic subset. Again, this is not a bug; determinism of the results is simply not guaranteed in such a case.

    So yes using LIMIT and ORDER BY together is defined.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search