GeekInterview.com
Series: Subject: Topic:
Question: 88 of 207

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@)?

Asked by: Interview Candidate | Asked on: Mar 20th, 2006
Showing Answers 1 - 10 of 10 Answers
legacy

Answered On : Mar 22nd, 2006

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.

  
Login to rate this answer.
Siva Reddy

Answered On : Mar 22nd, 2006

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

  
Login to rate this answer.
Culver_lake

Answered On : Mar 23rd, 2006

View all answers by Culver_lake

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?

  
Login to rate this answer.
javid

Answered On : Mar 27th, 2006

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

  
Login to rate this answer.
Culver_lake

Answered On : Mar 28th, 2006

View all answers by Culver_lake

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.

  
Login to rate this answer.
rajani

Answered On : May 11th, 2006

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!!

     

   

  
Login to rate this answer.
Nahidullah Khan

Answered On : Jun 1st, 2006

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.

  
Login to rate this answer.
Shatadru

Answered On : Jun 19th, 2006

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

  
Login to rate this answer.
kaviarasu

Answered On : Jun 23rd, 2006

SELECT  *  FROM  TABLE_NAME  AA

WHERE  5 = (SELECT  COUNT (FIRST_FIELD) FROM TABLE_NAME

                     WHERE FIRST_FIELD <= AA.FIRST_FIELD )

WITH UR;

  
Login to rate this answer.
dyury

Answered On : Mar 7th, 2010

View all answers by dyury

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

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

  
Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.