World Wide Tutorial

1. Background
In some RDBMS such as MySQL, to select a limited number of 'records', you can use an internal function' LIMIT () ', but this function is a kind of internal Trigger the RDBMS.
The Trigger adds a column to each table that you create, this column will be numbered in order to be able to make some sort.
This solution is a choice that very few RDBMS put in place.
1.1. Working Environment
For examples of this article, we will use the table 'EMPLOYEE', provided with the basis SAMPLE DB2. See code
2. First Fetch n Rows Only
2.1. Query base
Typically in DB2, to make a 'SELECT', we would like this

SELECT * FROM GG.EMPLOYEE

Or

SELECT EMPL.FIRSTNME, EMPL.LASTNAME
AS FROM GG.EMPLOYEE EMPL

This will return the whole of the table.
2.2. Select 10 lines
To recover the first 10 lines of your application, you must use the SQL Fetch First n Rows Only

SELECT EMPL.EMPNO, EMPL.FIRSTNME, EMPL.LASTNAME
AS FROM GG.EMPLOYEE EMPL
BY ORDER EMPL.EMPNO
FETCH FIRST 10 ROWS ONLY


To recover the last 10 lines, we must add an ORDER BY DESC

SELECT EMPL.EMPNO, EMPL.FIRSTNME, EMPL.LASTNAME
AS FROM GG.EMPLOYEE EMPL
BY ORDER EMPL.EMPNO DESC
FETCH FIRST 10 ROWS ONLY


3. Select a beach lines
Here, in order to select a range of lines, for example between 12 and 18 frontlines, it is necessary to call a SQL command, which will add a column to your query.
3.1. OVER ROW_NUMBER ()
The function OVER ROW_NUMBER (), will allow us to make this work.

SELECT ROW_NUMBER () OVER (ORDER BY EMPL.EMPNO) AS NUM, EMPL.EMPNO, EMPL.FIRSTNME, EMPL.LASTNAME
AS FROM GG.EMPLOYEE EMPL

Specifically here, we added a column NUM, which is a 'integer', starting from 1 to N.

To use this column, we need to encapsulate this request, in order to make treatment.

WITH TABLETMP (NUM, EMPNO, FIRST, NOM) AS
(
SELECT ROW_NUMBER () OVER (ORDER BY EMPL.EMPNO) AS NUM, EMPL.EMPNO, EMPL.FIRSTNME, EMPL.LASTNAME
AS FROM GG.EMPLOYEE EMPL
)
SELECT * FROM TABLETMP
WHERE NUM BETWEEN 12 AND 18

We have just returned from 7 lines of the line 12 to line 18.
The clause in OVER BY ORDER (), we guarantee the order of information.


To get the lines from the end, it launches a ORDER BY ........ DESC.

WITH TABLETMP (NUM, EMPNO, FIRST, NOM) AS
(
SELECT ROW_NUMBER () OVER (ORDER BY EMPL.EMPNO DESC) AS NUM, EMPL.EMPNO, EMPL.FIRSTNME, EMPL.LASTNAME
AS FROM GG.EMPLOYEE EMPL
)
SELECT * FROM TABLETMP
WHERE NUM BETWEEN 12 AND 18


4. Let us go further ........
We might need to repeat this kind of select in a draft could then make a stored procedure, instead of your programming language.

CREATE PROCEDURE "GG". "PROCEDURE1"
   (IN VMIN INT, IN VMAX INTEGER)
     DYNAMIC RESULT SETS 1
     LANGUAGE SQL
BEGIN
   
     Rs DECLARE CURSOR WITH RETURN FOR
         AS WITH TABLETMP
         (
             SELECT ROW_NUMBER () OVER (ORDER BY EMPL.EMPNO) AS NUM, EMPL.FIRSTNME, EMPL.LASTNAME
             AS FROM GG.EMPLOYEE EMPL
           
           
         )
         SELECT * FROM TABLETMP
         WHERE AND NUM BETWEEN VMIN VMAX
        
     OPEN rs;
END @

Then we call the procedure in passing two parameters, leaving the beach and the end.

CALL GG.PROCEDURE1 (10.12)

4.1. The total
And here is a stored procedure, which takes over as setting the table for research and the field that make the ORDER BY.

CREATE PROCEDURE "GG". "Procedural"
(IN VTABLE VARCHAR (255),
IN VORDERBY VARCHAR (255),
IN VMIN INT,
IN VMAX INTEGER
)
     DYNAMIC RESULT SETS 1
     LANGUAGE SQL
BEGIN

DECLARE STMT_STRING VARCHAR (500);
RC DECLARE CURSOR WITH RETURN FOR STMT_NAME;

SET STMT_STRING = '
         AS WITH TABLETMP
         (
             SELECT '| | VTABLE ||'.*, ROW_NUMBER () OVER (ORDER BY' | | VORDERBY | | ') AS NUM
             FROM '| | VTABLE | |'
         )
         SELECT * FROM TABLETMP
         WHERE NUM BETWEEN '| | CHAR (VMIN) | |' AND '| | CHAR (VMAX) | |'';

PREPARE STMT_NAME FROM STMT_STRING;
OPEN RC;
        
END @