Skip to content

Commit 98247bb

Browse files
Z95-cmykZ95-cmyk
committed
docs: explain Oracle-compatible ROWNUM
Assisted-by: OpenAI:gpt-5
1 parent 15d3b5b commit 98247bb

2 files changed

Lines changed: 163 additions & 2 deletions

File tree

‎doc/src/sgml/queries.sgml‎

Lines changed: 159 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1920,6 +1920,165 @@ SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- wrong
19201920
</sect1>
19211921

19221922

1923+
<sect1 id="queries-rownum">
1924+
<title>Oracle-Compatible <literal>ROWNUM</literal></title>
1925+
1926+
<indexterm zone="queries-rownum">
1927+
<primary>ROWNUM</primary>
1928+
</indexterm>
1929+
1930+
<para>
1931+
In Oracle-compatible databases, <productname>IvorySQL</productname>
1932+
provides the <literal>ROWNUM</literal> pseudocolumn. It has type
1933+
<type>bigint</type> and numbers rows in the order in which the current
1934+
query block produces them, starting with 1. The name is recognized
1935+
case-insensitively and must be unqualified.
1936+
</para>
1937+
1938+
<para>
1939+
A <literal>ROWNUM</literal> value is assigned after the row has satisfied
1940+
the other conditions in the same <literal>WHERE</literal> clause, but
1941+
before same-level grouping, aggregation, duplicate removal, or sorting.
1942+
Consequently, a condition such as <literal>ROWNUM &lt;= 5</literal>
1943+
selects the first five rows produced by the query block; it does not
1944+
select the first five rows in a later <literal>ORDER BY</literal> order.
1945+
</para>
1946+
1947+
<table id="queries-rownum-conditions">
1948+
<title>Common <literal>ROWNUM</literal> Conditions</title>
1949+
<tgroup cols="2">
1950+
<thead>
1951+
<row>
1952+
<entry>Condition</entry>
1953+
<entry>Effect</entry>
1954+
</row>
1955+
</thead>
1956+
<tbody>
1957+
<row>
1958+
<entry><literal>ROWNUM &lt;= <replaceable>n</replaceable></literal></entry>
1959+
<entry>Returns at most the first <replaceable>n</replaceable> rows.</entry>
1960+
</row>
1961+
<row>
1962+
<entry><literal>ROWNUM &lt; <replaceable>n</replaceable></literal></entry>
1963+
<entry>Returns at most <replaceable>n</replaceable> - 1 rows.</entry>
1964+
</row>
1965+
<row>
1966+
<entry><literal>ROWNUM = 1</literal></entry>
1967+
<entry>Returns at most the first row.</entry>
1968+
</row>
1969+
<row>
1970+
<entry><literal>ROWNUM = <replaceable>n</replaceable></literal>, where
1971+
<replaceable>n</replaceable> is greater than 1</entry>
1972+
<entry>Returns no rows.</entry>
1973+
</row>
1974+
<row>
1975+
<entry><literal>ROWNUM &gt; <replaceable>n</replaceable></literal>, where
1976+
<replaceable>n</replaceable> is at least 1</entry>
1977+
<entry>Returns no rows.</entry>
1978+
</row>
1979+
</tbody>
1980+
</tgroup>
1981+
</table>
1982+
1983+
<para>
1984+
Conditions that require the first candidate row to have a number greater
1985+
than 1 cannot become true. The first candidate is assigned number 1; if
1986+
it fails the condition, the next candidate is again considered for number
1987+
1. Conversely, <literal>ROWNUM &gt; 0</literal> and
1988+
<literal>ROWNUM &gt;= 1</literal> are true for every produced row.
1989+
</para>
1990+
1991+
<sect2 id="queries-rownum-top-n">
1992+
<title>Top-N and Pagination Queries</title>
1993+
1994+
<para>
1995+
To select rows after sorting them, perform the sort in an inner query
1996+
block and apply the <literal>ROWNUM</literal> condition in the outer
1997+
block. For example, the following query returns the three employees
1998+
with the highest salaries:
1999+
<programlisting>
2000+
SELECT *
2001+
FROM (
2002+
SELECT employee_id, salary
2003+
FROM employees
2004+
ORDER BY salary DESC
2005+
)
2006+
WHERE ROWNUM &lt;= 3;
2007+
</programlisting>
2008+
Writing the <literal>ROWNUM</literal> condition and
2009+
<literal>ORDER BY</literal> in the same query block would instead select
2010+
some three rows and then sort only those rows.
2011+
</para>
2012+
2013+
<para>
2014+
Pagination can use two nested query blocks. The inner
2015+
<literal>ROWNUM</literal> condition limits work to the requested upper
2016+
bound, while its select list preserves the assigned number for the outer
2017+
lower-bound condition:
2018+
<programlisting>
2019+
SELECT employee_id, salary
2020+
FROM (
2021+
SELECT page_source.*, ROWNUM AS row_number
2022+
FROM (
2023+
SELECT employee_id, salary
2024+
FROM employees
2025+
ORDER BY employee_id
2026+
) page_source
2027+
WHERE ROWNUM &lt;= 20
2028+
)
2029+
WHERE row_number &gt; 10;
2030+
</programlisting>
2031+
</para>
2032+
</sect2>
2033+
2034+
<sect2 id="queries-rownum-scope">
2035+
<title>Scope and Usage</title>
2036+
2037+
<para>
2038+
Each query block normally has its own counter. A subquery therefore
2039+
starts numbering at 1 independently of its parent query, and a correlated
2040+
subquery starts a new sequence each time it is invoked. Operands of
2041+
<literal>UNION</literal> and <literal>UNION ALL</literal> also have
2042+
independent counters.
2043+
</para>
2044+
2045+
<para>
2046+
<literal>ROWNUM</literal> can appear in a select list and in expressions,
2047+
and it can restrict <command>SELECT</command>, <command>UPDATE</command>,
2048+
and <command>DELETE</command> statements. For example:
2049+
<programlisting>
2050+
UPDATE work_queue
2051+
SET claimed = true
2052+
WHERE claimed = false
2053+
AND ROWNUM &lt;= 10;
2054+
</programlisting>
2055+
Without an ordering subquery, which ten qualifying rows are updated is
2056+
not defined.
2057+
</para>
2058+
2059+
<para>
2060+
In Oracle-compatible SQL, <literal>ROWNUM</literal> and
2061+
<literal>ROWID</literal> cannot be used as explicit column aliases or
2062+
table aliases. PL/iSQL declarations can use those names for local
2063+
variables and parameters. In PostgreSQL-compatible databases,
2064+
<literal>ROWNUM</literal> has no pseudocolumn meaning and can be used as
2065+
an ordinary identifier.
2066+
</para>
2067+
2068+
<note>
2069+
<para>
2070+
Currently, operands of <literal>INTERSECT</literal> and
2071+
<literal>EXCEPT</literal> share a <literal>ROWNUM</literal> counter.
2072+
A subquery used through <literal>LATERAL</literal> or
2073+
<literal>CROSS APPLY</literal> also does not restart its counter for
2074+
every outer row. Applications should not rely on independent numbering
2075+
in these cases.
2076+
</para>
2077+
</note>
2078+
</sect2>
2079+
</sect1>
2080+
2081+
19232082
<sect1 id="queries-limit">
19242083
<title><literal>LIMIT</literal> and <literal>OFFSET</literal></title>
19252084

‎doc/src/sgml/ref/select.sgml‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2169,8 +2169,10 @@ SELECT 2+2;
21692169
syntax is also used by <productname>IBM DB2</productname>.
21702170
(Applications written for <productname>Oracle</productname>
21712171
frequently use a workaround involving the automatically
2172-
generated <literal>rownum</literal> column, which is not available in
2173-
PostgreSQL, to implement the effects of these clauses.)
2172+
generated <literal>ROWNUM</literal> pseudocolumn to implement the effects
2173+
of these clauses. <productname>IvorySQL</productname> provides this
2174+
pseudocolumn in Oracle-compatible databases; see
2175+
<xref linkend="queries-rownum"/>.)
21742176
</para>
21752177
</refsect2>
21762178

0 commit comments

Comments
 (0)