Fetch nth maximum record using DB2 query

akshaiya · New User Joined: 17 Sep 2005 Posts: 49

I want to fetch nth maximum record using DB2 query in the cobol program.

Input File :

s-num ind pt-cd ssn

5353456 y t
5353456 y t
5353456 y t
6553452 y g
6553452 y g

Table :

s-num s1-num(CHAR 3) s2-num (CHAR 3)
5353456 999 876
5353456 999 875
5353456 999 874
5353456 999 873
5353456 999 872
6553452 AA 103
6553452 AA 102
6553452 AA 101

Output file :

s-num ind pt-cd ssn SSN=(s1-num appended with s2-num)

5353456 y t 999876
5353456 y t 999875
5353456 y t 999874
6553452 y g AA103
6553452 y g AA103

First record should fetch first maximum and the second record should fetch second maximum and nth record should fetch nth maximum.

Is it possible with the query?

dick scherrer · Posted: Tue Mar 06, 2007 8:52 am

Hello,

It would be fairly simple if you use a cursor.

Is there any reason that you cannot use a cursor?

vini_srcna · Posted: Tue Mar 06, 2007 3:11 pm

I think he is talking about the select statement to put in the DECLARE cursor. I guess he is using cursor as he is talking about FETCH & COBOL.

You can do that using the ORDER BY.

EXEC SQL DECLARE CUR1 CURSOR FOR
SELECT s-num,ind, pt-cd, s1-num||s2-num FROM TABLE NAME
WHERE condition
ORDER BY 1,2,3,4.

The fetch of this cursor in the cobol program should give the results you are expecting. Let me know if this doesnt work. Thanks