how to do row number query

[xixi]

i want to use sql query to open a resultset, say i want the cursor
point to whatever position i start with by giving a row number , so is
there anyway i can use the sql function to do that, so when the
resultset return, the first row will be the absolute row dertermine by
the row number

[Knut Stolze]

xixi wrote:

i want to use sql query to open a resultset, say i want the cursor
point to whatever position i start with by giving a row number , so is
there anyway i can use the sql function to do that, so when the
resultset return, the first row will be the absolute row dertermine by
the row number

I don't understand your question, but you can use the row_number() function
to add a unique number for each row in the result set.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Knut Stolze]

xixi wrote:

i want to use sql query to open a resultset, say i want the cursor
point to whatever position i start with by giving a row number , so is
there anyway i can use the sql function to do that, so when the
resultset return, the first row will be the absolute row dertermine by
the row number

I don't understand your question, but you can use the row_number() function
to add a unique number for each row in the result set.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Serge Rielau]

Also look into "scrollable cursors".

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Also look into "scrollable cursors".

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[xixi]

what i need to do is similar the way relative record number on AS400,
say i want the cursor point to row number 5, so i get to open the
result set by some kind of function , tell the resultset i want the
cursor point to record number 5 (absolute record number), but i can't
find row_number function being available on DB2. can you tell me how
exactly to row_number function with sql

[xixi]

what i need to do is similar the way relative record number on AS400,
say i want the cursor point to row number 5, so i get to open the
result set by some kind of function , tell the resultset i want the
cursor point to record number 5 (absolute record number), but i can't
find row_number function being available on DB2. can you tell me how
exactly to row_number function with sql

[Jan M. Nelken]

xixi wrote:

what i need to do is similar the way relative record number on AS400,
say i want the cursor point to row number 5, so i get to open the
result set by some kind of function , tell the resultset i want the
cursor point to record number 5 (absolute record number), but i can't
find row_number function being available on DB2. can you tell me how
exactly to row_number function with sql

Exactly what is the meaning of "row number" in - by definition unordered
- set?

Technically speaking same query may produce result set in any arbitrary
order - unles *you specifically* request ORDER BY to present rows in
specific order. Within that order - duplicate rows may be presented
again in any arbitrary and undetermined order.

So if you are speaking about SQL data - you are speaking about "rows"
which are members in a set.

When you use wording "absolute record number" - you are talking flat
file records and COBOL way to access them.

You can "number" rows in an answer set by using row_number() function -
as Serge suggested. This numbeing is a tmporary mapping in a given
instance of an answer set.

Jan M. Nelken

[Jan M. Nelken]

xixi wrote:

what i need to do is similar the way relative record number on AS400,
say i want the cursor point to row number 5, so i get to open the
result set by some kind of function , tell the resultset i want the
cursor point to record number 5 (absolute record number), but i can't
find row_number function being available on DB2. can you tell me how
exactly to row_number function with sql

Exactly what is the meaning of "row number" in - by definition unordered
- set?

Technically speaking same query may produce result set in any arbitrary
order - unles *you specifically* request ORDER BY to present rows in
specific order. Within that order - duplicate rows may be presented
again in any arbitrary and undetermined order.

So if you are speaking about SQL data - you are speaking about "rows"
which are members in a set.

When you use wording "absolute record number" - you are talking flat
file records and COBOL way to access them.

You can "number" rows in an answer set by using row_number() function -
as Serge suggested. This numbeing is a tmporary mapping in a given
instance of an answer set.

Jan M. Nelken

[Serge Rielau]

Take a look at ROW_NUMBER() OVER(...)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Take a look at ROW_NUMBER() OVER(...)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[xixi]

here is the definition of rrn() function on iSeries DB2,

The RRN function returns the relative record number of a row.

and here is the example
Return the relative record number and employee name from table
EMPLOYEE for those employees in department 20.
SELECT RRN(EMPLOYEE), LASTNAME
FROM EMPLOYEE
WHERE DEPTNO = 20

so we don't care about order by, we want to know the row number of
this table meet the criteria, is DB2 UDB on windows ( i am using db2
v8.1) has same function? thanks

[xixi]

here is the definition of rrn() function on iSeries DB2,

The RRN function returns the relative record number of a row.

and here is the example
Return the relative record number and employee name from table
EMPLOYEE for those employees in department 20.
SELECT RRN(EMPLOYEE), LASTNAME
FROM EMPLOYEE
WHERE DEPTNO = 20

so we don't care about order by, we want to know the row number of
this table meet the criteria, is DB2 UDB on windows ( i am using db2
v8.1) has same function? thanks

[Knut Stolze]

xixi wrote:

here is the definition of rrn() function on iSeries DB2,

The RRN function returns the relative record number of a row.
What is the "relative record number"? Relative to what?
and here is the example

Return the relative record number and employee name from table
EMPLOYEE for those employees in department 20.
SELECT RRN(EMPLOYEE), LASTNAME
FROM EMPLOYEE
WHERE DEPTNO = 20

so we don't care about order by, we want to know the row number of
this table meet the criteria, is DB2 UDB on windows ( i am using db2
v8.1) has same function? thanks

I really don't know exactly what you want to have in the result, but you
could easily do this:

SELECT rrn, lastname
FROM ( SELECT row_number() over (), lastname, deptno
FROM employee ) AS t(rrn, lastname, deptno)
WHERE deptno = 20
If you want to get a "row number" that indicates the relative position of
the physical row with respect to the beginning of the table, then you
should be aware that those numbers might change quite easily on
insert/update/delete operations, reorgs, ...

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Knut Stolze]

xixi wrote:

here is the definition of rrn() function on iSeries DB2,

The RRN function returns the relative record number of a row.
What is the "relative record number"? Relative to what?
and here is the example

Return the relative record number and employee name from table
EMPLOYEE for those employees in department 20.
SELECT RRN(EMPLOYEE), LASTNAME
FROM EMPLOYEE
WHERE DEPTNO = 20

so we don't care about order by, we want to know the row number of
this table meet the criteria, is DB2 UDB on windows ( i am using db2
v8.1) has same function? thanks

I really don't know exactly what you want to have in the result, but you
could easily do this:

SELECT rrn, lastname
FROM ( SELECT row_number() over (), lastname, deptno
FROM employee ) AS t(rrn, lastname, deptno)
WHERE deptno = 20
If you want to get a "row number" that indicates the relative position of
the physical row with respect to the beginning of the table, then you
should be aware that those numbers might change quite easily on
insert/update/delete operations, reorgs, ...

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[xixi]

Relative record numbers identify the positions of the records relative
to
the beginning of the file. For example, the relative record
numbers of
the first, fifth, and seventh records are 1, 5, and 7,
respectively. so i don't select the relative record number, i need to
use relative record number in where clause to retrieve the row like
this sql worked on AS400:

select * from table where rrn(table)=1, please help

[xixi]

Relative record numbers identify the positions of the records relative
to
the beginning of the file. For example, the relative record
numbers of
the first, fifth, and seventh records are 1, 5, and 7,
respectively. so i don't select the relative record number, i need to
use relative record number in where clause to retrieve the row like
this sql worked on AS400:

select * from table where rrn(table)=1, please help

[xixi]

is there anybody have solutions to achieve this by use sql query to
get the data for the row number i passed , which means if i want the
10th row of the data, i can select from it? thanks
da****@yahoo.com (xixi) wrote in message news:<c0**************************@posting.google. com>...

Relative record numbers identify the positions of the records relative
to
the beginning of the file. For example, the relative record
numbers of
the first, fifth, and seventh records are 1, 5, and 7,
respectively. so i don't select the relative record number, i need to
use relative record number in where clause to retrieve the row like
this sql worked on AS400:

select * from table where rrn(table)=1, please help

[Ian]

xixi wrote:

is there anybody have solutions to achieve this by use sql query to
get the data for the row number i passed , which means if i want the
10th row of the data, i can select from it? thanks

A table in of itself has no order, but you can write a query to
get the 10th row of an ordered result set:

select
name,
score
from (
select
name,
score,
rownumber() over (order by score desc) as rn
from
test_scores
) a
where
a.rn = 10;

If you do not specify an order for the rownumber() function, you are
not guaranteed to get the same answer each time you execute the query.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Jean-David Beyer]

xixi wrote:

is there anybody have solutions to achieve this by use sql query to
get the data for the row number i passed , which means if i want the
10th row of the data, i can select from it? thanks

There is no 10th row fo the data. Relations are _unordered sets_. If you
wish to number all the rows (by establishing an attribute with row number
as a value), then I suggest you do so, and make an index on it. Then you
could retrive any row number you wish efficiently.

Of course that opens up a can of worms (Remember Edsel Murphy's Law? When
you open a can of worms, to recan them takes a larger size can.): what if
you have 100 rows in a relation numbered 1 to 100. Now you delete row
numbered 42. Now what is the 50th row? What do you mean by it? Because if
you do not mean the row with the number 50 in it, you will have to
renumber all the rows after the delete. Hardly efficient.

Easier to remember that relations are unordered sets and there is no such
thing as row # N. You can order them with ORDER BY in a SELECT statement
and count your way. Or if you are lucky, you can diddle your ORDER BY so
that the row you want is first.

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 21:00:00 up 3 days, 5:59, 6 users, load average: 4.17, 4.15, 4.13