How to get the 5th record ?

webxpertin · New User Joined: 05 Apr 2005 Posts: 9 Location: india

How to get the 5th record from a DB2 table or a 100th record from a vsam file directly without knowing any key of that particular record?
Thanks

Abirami.YN · Posted: Wed Jul 20, 2005 7:25 pm

Hi,

Try the following in DB2

SELECT *
FROM (SELECT
ROW_NUMBER() OVER (ORDER BY empno ASC) AS rownumber,
empno, firstnme
FROM edwarde.employee
) AS foo
WHERE rownumber = 5;

After executing this query , tell me ur result (I am not sure)

shobam · New User Joined: 18 Jul 2005 Posts: 34 Location: CN

You can find the Nth record or a range of records from a table using the ROW_NUMBER as abirami mentioned.

To know about ROW_NUMBER go thru

www.cis.unisa.edu.au/oracle/server.101/b10759/functions122.htm

Good Example is available in

blogs.conchango.com/jamiethomson/archive/2005/02/16/1025.aspx

Gops · New User Joined: 21 Jul 2005 Posts: 23

Hi Guys,

Tried with both of your examples,
but I am getting a syntax error on the over clause of the query.

Cheers,
Gopal

vasudev9 · New User Joined: 02 Aug 2005 Posts: 1 Location: india

How to get the 5th record from a DB2 table or a 100th record from a vsam file directly without knowing any key of that particular record?

Hi

To get 100th record from a vsam file, try this in IDCAMS

REPRO -
INFILE(DD1) -
OUTFILE(DD2) -
SKIP(99) -
COUNT(1)

This example SKIPS 99 Records and copies next 1 record.

Hope this do..

vasudev

withnams · New User Joined: 06 Jul 2005 Posts: 26 Location: Chennai

It depends upon the version of the DB2 you are using?

vijayamadhuri · Active User Joined: 06 Apr 2005 Posts: 180

to obtain the 5 th record use the following

EXEC SQL FETCH ABSOLUTE +5 C1 INTO :HVDEPTNO, :DEPTNAME, :MGRNO;

To fetch the fifth row from the end of the result table, use this FETCH statement:
EXEC SQL FETCH ABSOLUTE -5 C1 INTO :HVDEPTNO, :DEPTNAME, :MGRNO;

In this way u can fetch any row u require