ROW_NUMBER () in DB2

Question

How to use ROW_NUMBER () in a where clause in a DB2 database. I tried below, but this did not work:

SELECT * FROM CSPAPP.LOCATIONS WHERE (ROW_NUMBER() OVER(ORDER BY LOCATION)) BETWEEN 100 AND 200 

This gave an error: misuse of aggregate function or OLAP function.

I also tried using the following methods:

 SELECT (ROW_NUMBER() OVER(ORDER BY LOCATION)) AS RN ,* FROM CSPAPP.LOCATIONS WHERE RN < 200 SELECT (ROW_NUMBER() OVER(ORDER BY LOCATION)) AS RN ,LOCATION FROM CSPAPP.LOCATIONS WHERE RN < 200 

Answer 1

You cannot refer to an alias at the same level where it is defined. You need to wrap this in a view:

 SELECT location FROM ( SELECT row_number() over(order by location) as rn, location FROM cspapp.locations ) WHERE rn < 200 

Answer 2

I use something similar when choosing based on row number in DB2 iSeries:

 SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY location) as RRN, * FROM CSPAPP.LOCATIONS ) WHERE RRN between 100 and 200 

If you are only interested in field 1, you can assign a name to select and link to fields:

 SELECT DATA.location FROM ( SELECT ROW_NUMBER() OVER(ORDER BY location) as RRN, * FROM CSPAPP.LOCATIONS ) as DATA WHERE DATA.RRN between 100 and 200 

Answer 3

You can try FETCH FIRST 200 ROWS ONLY instead of row_number. Write your choice, as usual, without ROW_NUMBER, order everything you need and FETCH FIRST x.

Selecting all columns with "*" is not good practice, especially if you have more than 600 columns (and this in itself is a poor database design).

Answer 4

Without using the row_number () function:

 SELECT * FROM (SELECT * FROM CSPAPP.LOCATIONS ORDER BY LOCATION FETCH FIRST 200 rows only) ORDER BY LOCATION DESC FETCH FIRST 100 rows only; With Row number: SELECT ROW_NUMBER() OVER(ORDER BY LOCATIONS), LOCATIONS as RNM FROM (SELECT * FROM CSPAPP.LOCATIONS ORDER BY LOCATIONS FETCH FIRST 200 rows only) ORDER BY LOCATIONS DESC FETCH FIRST 100 rows only; 

Answer 5

You can refer to an alias at the same level at which it is defined. You need to wrap this in a view:

 SELECT T1.* FROM( SELECT row_number() over(order by location) as rn ,L.* FROM cspapp.locations L) As T1 WHERE T1.rn < 200 

But you must understand that * will never be the best practice. You should use the column name, not * ( L.col1 ).