473,889 Members | 1,361 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

how to do row number query

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
Nov 12 '05
19 81890
Take a look at ROW_NUMBER() OVER(...)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #11
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
Nov 12 '05 #12
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
Nov 12 '05 #13
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
Nov 12 '05 #14
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
Nov 12 '05 #15
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
Nov 12 '05 #16
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
Nov 12 '05 #17
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.co m (xixi) wrote in message news:<c0******* *************** ****@posting.go ogle.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

Nov 12 '05 #18
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! =-----
Nov 12 '05 #19
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

Nov 12 '05 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
432
by: xixi | last post by:
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
1
2490
by: David Barger | last post by:
Greetings, It appears that an Append Query I run in Access XP is randomly failing to append a field. I have payroll data being entered into a payroll database. This data is exported daily to two csv files. (One for updated data, and the other for unupdated data.) The CSV files are attached to my Jobcosting database. After the CSV
2
2674
by: nettid1 | last post by:
I'm a little new to Access so please bear with me... I have a query in Access consisting of three fields: Account Number, Query ID and Description. I want to have a button on a form that calls an Event Procedure. So far, so good. In this Event Procedure, I want to do the following (psuedocode) for each record in query
2
5077
by: bobabooey2k | last post by:
I have an update query with one field having in its "Update to" cell a DLookup statement. This query takes 2-3 minutes on 3000 records. Can I avoid dlookup here using multiple queries? An underlying subquery to this Update query involves a MAX function on a date field, which is then used in the DLookup statement. Any help appreciated. Thanks Richard
10
6943
by: BillCo | last post by:
I've been fishing in the collective knowledge pool of cdma posts gone by for a solution to this for a while - but no joy. Needless to say, I've also done my own head in trying to figure this out. Basically, I need to run a query agains a table that can identify what row a value is on. This would be easy enough - except it's to be run againt a number of indeterminate tables or indeterminate primary keys. It's not that the tables arent...
6
1884
by: ngweixiong | last post by:
Hi, I have 2 query in Ms Access. Query1: Summary of Invoice (ZEAVI) Fields: Biling doc Count of biling doc Date Order number Count of Order number
3
1487
by: clickingwires | last post by:
Is there anyway to number a query's rows? Like there are 5 records and you add a field to the query that counts them, 1, 2, 3, ...
2
2636
by: wizdom | last post by:
I'm trying to add simple page support, the page is php/mssql based. Anyone know why this doesn't work? I found the "NOT IN" command online but I'm not 100% sure about it..... any help is appreciated Currently its displaying the same results every time, no matter what "page" i put it on, obviously not the desired results, i'm familiar with the 'LIMIT' command in mysql, but that doesn't work in mssql.....here's my code i'm trying: ...
11
3341
by: browndudley | last post by:
I searched the forum and found something very close to helping me with what I'm trying to do but not exactly. I need to sequentially group number the results in an access query as if by grouping by PUR_DATE AND CSCODE - but I do not want to group the query... So that a field "GRP" would yield results like this: PUR_DATE CSCODE GRP 1/5/2009 1456 1 1/5/2009 1456 1 1/5/2009 1456 1 1/5/2009 985 2 1/5/2009 985 2
0
11188
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10889
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10441
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9606
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7150
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5828
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4647
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4251
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3255
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.