SQL - selecting a specific row number
tama mcconnell
Is it possible, with DB2/400 SQL, to select a specifc row by using its
relative record number,
for instance in oracle SQL I can go:
select * from file where rownum = 10, will return the 10th row in the
table
Thanks for your help.
Tama McConnell
Dieter Bender
use RRN(FileName)
Dieter
Paul Nicolay
Did you know that all IBM manuals are online at
http://as400bks.rochester.ibm.com ?
The DB2/400 SQL manual explains how to use RRN().
In your case SELECT * FROM table WHERE RRN(table) = 10
Regards,
Paul
-----------------------------
tama mcconnell wrote in message <01bf3ab4$77ba8520$53cfab92@TC030116>...
Hello,
Is it possible, with DB2/400 SQL, to select a specifc row by using its
relative record number,
for instance in oracle SQL I can go:
select * from file where rownum = 10, will return the 10th row in the
table
Thanks for your help.
Tama McConnell
The contents of this message express only the sender's opinion.
This message does not necessarily reflect the policy or views of
my employer, Merck & Co., Inc. All responsibility for the statements
made in this Usenet posting resides solely and completely with the
sender.
Carlopoli
Hi, this's an example for retrive the 10th row in the table... in DB2/400
SQL
D sql_num C Const(2)
*
D MyStruct DS OCCURS(10)
D fld0001 15A
D fld0002 30A
*
D MyArray DS OCCURS(10)
D InfDs 4B 0 Dim(2)
*
C/EXEC SQL INCLUDE SQLDA
C/END-EXEC
*
C Eval sqld = 2
C Eval sqln = 2
C Eval sqldabc = 136
*
C Eval sqltype = 453
C Eval sqllen = 15
C Eval sql_var(1) = sqlvar
*
C Eval sqltype = 453
C Eval sqllen = 30
C Eval sql_var(2) = sqlvar
*
*...Dichiaro cursore per selezione.
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR SELECT myFld01, myFld02 FROM MyLibl/MyFiles
C/END-EXEC
*
*...Apertura cursore.
C/EXEC SQL OPEN C1
C/END-EXEC
*
*...se ci sono errori FINE.
C/EXEC SQL WHENEVER SQLERROR GOTO ERRORE
C/END-EXEC
*
C/EXEC SQL
C+ FETCH C1 FOR 10 ROWS USING DESCRIPTOR :SQLDA INTO :myStruct:myArray
C/END-EXEC
*
C Do SQLERRD(3) Inx 6 0
C Inx Occur myStruct
C...
C EndDo
*
C ERRORE TAG
*
C Eval *InLR = *On
tama mcconnell <tama.mc...@telecom.co.nz> wrote in message
news:01bf3ab4$77ba8520$53cfab92@TC030116...
Phillip Lyles
The SQL would take the form of the following:
Select RRN(myfile) from myfile where "condition"
This will return all the the row numbers from myfile that meet the "where"
condition
tama mcconnell
Tama
Paul Nicolay <removethis....@merck.com> wrote in article
<8201dj$a41$1...@merck.com>...
Thomas
Given the way you worded your question, yes, it is possible by using the
RRN() function. But keep in mind that this function does in fact return
a physical relative record number, not a row number. When records are
deleted, the record numbers of the remaining records do not change
unless the file is reorganized. Also, an ORDER BY does not affect RRN();
the first physical record still returns RRN()=1 no matter what row it is
in the result table.
Tom Liotta
In article <01bf3ab4$77ba8520$53cfab92@TC030116>,
"tama mcconnell" <tama.mc...@telecom.co.nz> wrote:
> Hello,
>
> Is it possible, with DB2/400 SQL, to select a specifc row by using
its
> relative record number,
> for instance in oracle SQL I can go:
>
> select * from file where rownum = 10, will return the 10th row in the
> table
>
> Thanks for your help.
>
> Tama McConnell
>
--
Tom Liotta
AS/400 systems programmer
Sent via Deja.com http://www.deja.com/
Before you buy.
sharewi...@gmail.com
Thank you.
On Tuesday, November 30, 1999 1:30:00 PM UTC+5:30, Dieter Bender wrote:
> Hi,
> use RRN(FileName)
> Dieter
>
CRPence
>
> On Tuesday, November 30, 1999 UTC+5:30, Dieter Bender wrote:
>>
>> tama mcconnell wrote:
>>>
>>> Is it possible, with DB2/400 SQL, to select a specific row by
>>> can go:
>>>
>>> select * from file where rownum = 10, will return the 10th row in
>>> the table
>>>
>>
>>
>
> 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
Dr.UgoGagliardelli
> On 26-Aug-2014 09:49 -0500, sharewi...@gmail.com wrote:
>>
>> On Tuesday, November 30, 1999 UTC+5:30, Dieter Bender wrote:
>>>
>>> tama mcconnell wrote:
>>>>
>>>> Is it possible, with DB2/400 SQL, to select a specific row by
>>>> using its relative record number, for instance in oracle SQL I
>>>> can go:
>>>>
>>>> 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...
> 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
>
Year 1999 was a version 4 year, row_number was released in V5R4, so at
that time the only option was rrn.