Strictly speaking, that limitation makes sense because the above
-- Get arbitrarily numbered row_numbers SELECT ROW_NUMBER() OVER () -- Skip arbitrary rows SELECT a FROM (VALUES (1), (2), (3), (4)) t(a) OFFSET 3 ROWS
OFFSETexpressions are non-deterministic. Two subsequent executions of the same query might produce different results. But then again, any
ORDER BYclause is non-deterministic, if you do not order by a strictly
UNIQUEexpression, such as a primary key. So, that’s a bit of a pain, because other databases aren’t that strict and after all, you might just not care about explicit ordering for a quick, ad-hoc query, so a “reasonable”, lenient default would be useful.
Constant ORDER BY clauses don’t workYou cannot add a constant
ORDER BYclause to window functions either. I.e.:
-- This doesn't work: SELECT ROW_NUMBER() OVER (ORDER BY 'a') -- But this does! SELECT a FROM (VALUES (1), (2), (3), (4)) t(a) ORDER BY 'a' OFFSET 3 ROWS
ORDER BY 'a'uses a constant
VARCHARexpression, not a numeric one, as that would be generating column-reference-by-index expressions, which would be non-constant in the second example.
Random column references don’t workSo you’re thinking that you can just add a random column reference? Sometimes you can, but often you cannot:
The above examples show that you do not always have a column reference available in any given SQL expression. There is no useful column that you could refer to from the
-- This doesn't work: SELECT ROW_NUMBER() OVER ( ORDER BY [no-column-available-here] ) -- But this does! SELECT a FROM (VALUES (1), (2), (3), (4)) t(a) ORDER BY a OFFSET 3 ROWS
ROW_NUMBER()function. At the same time, you can write
ORDER BY ain the second example, but only if
ais a “comparable” value, i.e. not a LOB, such as
image. Besides, as we don’t really care about the actual ordering, is it worth ordering the result set by anything at all? Do you happen to have an index on
Quasi-constant ORDER BY expressions do workSo, to stay on the safe side, if ever you need a dummy
ORDER BYexpression in SQL Server, use a quasi-constant expression, like
@@language, or any of these). The following will always work:
From the upcoming jOOQ 3.4, we’ll also generate such synthetic
-- This always works: SELECT ROW_NUMBER() OVER (ORDER BY @@version) -- So does this: SELECT a FROM (VALUES (1), (2), (3), (4)) t(a) ORDER BY @@version OFFSET 3 ROWS
ORDER BYclauses that will help you simplify writing vendor-agnostic SQL in these edge-cases, as we believe that you simply shouldn’t think of these things all the time.