Hacker News new | ask | show | jobs
by dkubb 2061 days ago
I agree in this specific example the inner sort is meaningless. There are valid cases where a subquery can use an ORDER BY, such as when a LIMIT or OFFSET is specified.

I'd go to far to say that unless a query has an ORDER BY it is likely a bug to use LIMIT or OFFSET. In the absence of an ORDER BY the database is free to sort the rows any way it likes, and this could change between versions or based on any number of implementation details. If it appears to sort determinsitically with no ORDER BY it should not be relied upon.
1 comments
throwaway_pdp09 2061 days ago
> valid cases where a subquery can use an ORDER BY, such as when a LIMIT or OFFSET is specified.

Oh, quite true! However the output of that subquery, despite having an order by, will not have a guaranteed order. Ordering is lost as the result set leaves the subquery. This is in the sql standard and in most if not all implementations.

> I'd go to far to say that unless a query has an ORDER BY it is likely a bug to use LIMIT or OFFSET

Wholly agreed.

link