Retrieving fetched data a second time in embedded SQL applications

The technique that you use to retrieve data a second time depends on the order in which you want to see the data again.

Procedure

You can retrieve data a second time by using any of the following methods:

Retrieve data from the beginning
To retrieve the data again from the beginning of the result table, close the active cursor and reopen it. This action positions the cursor at the beginning of the result table. But, unless the application holds locks on the table, others may have changed it, so what had been the first row of the result table may no longer be.
Retrieve data from the middle
To retrieve data a second time from somewhere in the middle of the result table, issue a second SELECT statement and declare a second cursor on the statement. For example, suppose the first SELECT statement was:
```
     SELECT * FROM DEPARTMENT 
       WHERE LOCATION = 'CALIFORNIA' 
       ORDER BY DEPTNO
```
Now, suppose that you want to return to the rows that start with DEPTNO = 'M95' and fetch sequentially from that point. Code the following statement:
```
     SELECT * FROM DEPARTMENT 
       WHERE LOCATION = 'CALIFORNIA' 
       AND DEPTNO >= 'M95' 
       ORDER BY DEPTNO
```
This statement positions the cursor where you want it.
Retrieve data in reverse order
Ascending ordering of rows is the default. If there is only one row for each value of DEPTNO, then the following statement specifies a unique ascending ordering of rows:
```
     SELECT * FROM DEPARTMENT 
       WHERE LOCATION = 'CALIFORNIA' 
       ORDER BY DEPTNO
```
To retrieve the same rows in reverse order, specify that the order is descending, as in the following statement:
```
     SELECT * FROM DEPARTMENT 
       WHERE LOCATION = 'CALIFORNIA' 
       ORDER BY DEPTNO DESC
```
A cursor on the second statement retrieves rows in exactly the opposite order from a cursor on the first statement. Order of retrieval is guaranteed only if the first statement specifies a unique ordering sequence.

For retrieving rows in reverse order, it can be useful to have two indexes on the DEPTNO column, one in ascending order, and the other in descending order.