How to retrieve n-th row ie n-th record of any table irrespective of the columns and the values present in the table without using ROWID(As it is not supported in DB@)?

This could be logic if u want it in cobol program , U use FETCH FIRST N ROWS ONLY . here n can take values of desired row no.then u can loop thru cursor till end of row . for making N as user supplied u need to go for dynamic sql in which u ll build whole sql string .

or u can use cursor scrolling options in the fetch statement.

Declare a Cursor and fetch rows by descending. This will get the Nth row on First Instance. But Guess this Query degrades Performance

Without getting too theoretical, there is no Nth row of a relation. There is no first row, no last row etc.  Order of the rows and columns are undefined concepts. 

In order to retrieve any row you need to have values that can be compared (ie retrieved via the equality relation). The fact that a column equals a value does not depend on order in a relational system.  Even system that support a pseudo column like row_number or something similar are equating values.

Ask yourself the question: how do I know I want the nth row without knowing previously what identifies it?

To retrieve the nth row from table you can code Query like

SELECT * FROM TABLE-NAME WHERE NTH=(SELECT COUNT(*) FROM TABLE WHERE COUNT >2)

IF WROUNGH FEED BACK ME

sorry it's wrong.  in your example NTH would have to be a column name of the table which doesn't represent the nTH row.

sir EDid ,

 I dont understand the problem in asking for nth row of a table. we can simply ask for 10th row of a table ,and we can try to obtain it using the rownum value.

  like ---

    select * from emp where rownum = 10;

   whats wrong with this query???please suggest!!

     

   

i think u can use following sql statement to retreive nth (5th for example) row of any table

select * from <table-name> where rrn(table-name) = 5

this command will work atleast on db2/400 sql

THANKS.

This is a fallacious question. IN rdbms, by definition, there is no nth row, as the ordering of the rows is immaterial

SELECT  *  FROM  TABLE_NAME  AA

WHERE  5 = (SELECT  COUNT (FIRST_FIELD) FROM TABLE_NAME

                     WHERE FIRST_FIELD <= AA.FIRST_FIELD )

WITH UR;

We can use scrollable cursor ( with DB2 V8 and higher).

FETCH FIRST ROWSET STARTING AT ABSOLUTE N FROM CURSOR1 FOR 1 ROW.