SQL - selecting a specific row number

CRPence

2014-08-26 16:37:08 UTC

Is it possible, with DB2/400 SQL, to select a specific row by
using its relative record number, for instance in oracle SQL I
select * from file where rownum = 10, will return the 10th row in
the table

use RRN(FileName)

Thank you.

Given this thread has been resurrected [as a teenager nonetheless], I
suppose no harm in adding...

Use of a correlation-name for a table-reference is typically the
better choice over using the file-name; a change to the table-reference
for a specified table-name and\or schema-name in a subquery will not
necessitate the _same revision_ being made in every other reference,
such as any RRN(table-reference) of an expression\column-list of a
SELECT, of a predicate in a WHERE clause, et al. For example, c_id is
the correlation identifier used instead of the qualified file name in
the following SELECT query [a query similar to the one presented by the OP]:

select c_id.*
from schema_name.table_name AS c_id
where RRN(c_id) = 10

The RRN is a far-from-relational aspect of the database, per the
physical ordering of the rows being contrary to a relational
perspective; physical data generally should be considered unordered
sets, such that no dependence should be coded on the physical order.
Use of RRN() should be very atypical\unusual in any /normal/ SQL
processing; referenced only in something like _tools_ that might need to
represent physical layout of data, and as such are unlikely to be
platform independent nor expected to be predictable as to what any
particular tuple might be at any particular RRN().

Often OLAP queries will be utilized for ordered data that since has
been assigned a ROW_NUMBER or a RANK, allowing for a more sensible
representation, for which the same set of data would produce the same
result-set from the query irrespective the underlying physical ordering
according to a RRN; while still not a relational aspect, the reporting
[irrespective platform or how data was loaded and modified] would at
least be predictable as contrasted with using an RNN() that could easily
change [be unpredictable] for a variety of reasons whilst the data set
remains completely valid [irrespective of order].

I am unsure if the _rownum_ of the query from the OP is an implicit
OLAP reference [if so, then by what other than physical order would be
implied?], or if instead, the _rownum_ is an equivalent of the RRN() of
DB2 for i [for the physical relative row number]. The following
[untested] is an example of retrieving the 10th row_number of row-data
that has been numbered according to the order of the some_column values
of the named_table [though order of the derived table is undefined, for
lack of an ORDER BY]:

select *
from table
( select
c_id.*
, row_number() over(order by some_column) as myRRN
from schema_name.named_table AS c_id
) as dt
where dt.myRRN = 10

--
Regards, Chuck