16f6c0aecf
Inline adoc files when they are included exactly once. Also fix language tags because this inlining gives us better information on what language the code is written in.
67 lines
1.6 KiB
Plaintext
67 lines
1.6 KiB
Plaintext
== Why is this an issue?
|
|
|
|
Oracle's ``++ROWNUM++`` is a pseudo column that numbers the rows in a result set. Unfortunately, it numbers the rows in the set _before_ ordering is applied. So combining the two in the same query won't get you the results you expect. Instead, you should move your selection and ordering into a subquery, and use ``++ROWNUM++`` only on the outer query.
|
|
|
|
|
|
=== Noncompliant code example
|
|
|
|
[source,sql]
|
|
----
|
|
SELECT fname, lname, deptId
|
|
FROM employee
|
|
WHERE rownum <= 10
|
|
ORDER BY salary -- Noncompliant
|
|
----
|
|
|
|
|
|
=== Compliant solution
|
|
|
|
[source,sql]
|
|
----
|
|
SELECT *
|
|
FROM ( SELECT fname, lname, deptId
|
|
FROM employee
|
|
ORDER BY salary
|
|
)
|
|
WHERE rownum <= 10
|
|
----
|
|
|
|
|
|
ifdef::env-github,rspecator-view[]
|
|
|
|
'''
|
|
== Implementation Specification
|
|
(visible only on this page)
|
|
|
|
=== Message
|
|
|
|
Order your results in a subquery and apply "rownum" at the level of the outer query.
|
|
|
|
|
|
=== Highlighting
|
|
|
|
* primary: order by
|
|
* secondary: rownum|row_number()
|
|
|
|
|
|
'''
|
|
== Comments And Links
|
|
(visible only on this page)
|
|
|
|
=== on 27 Jun 2017, 15:57:56 Pierre-Yves Nicolas wrote:
|
|
\[~ann.campbell.2] The title may be misleading. Some other possibilities:
|
|
|
|
* "ROWNUM should not be used at the same query level as ORDER BY"
|
|
* or "ROWNUM should not be used with ORDER BY without a subquery"
|
|
|
|
|
|
=== on 27 Jun 2017, 16:09:55 Ann Campbell wrote:
|
|
\[~pierre-yves.nicolas]: "ORDER BY" should not be applied to queries that use "ROWNUM"
|
|
|
|
?
|
|
|
|
=== on 27 Jun 2017, 16:26:11 Pierre-Yves Nicolas wrote:
|
|
\[~ann.campbell.2] "ORDER BY should not be applied on top of a ROWNUM-based WHERE"? :)
|
|
|
|
endif::env-github,rspecator-view[]
|