How to use ROW_NUMBER() in where clause in DB2 database.I have tried below but it did not work:
SELECT * FROM CSPAPP.LOCATIONSWHERE (ROW_NUMBER() OVER(ORDER BY LOCATION)) BETWEEN 100 AND 200It gave error : Invalid use of aggregate function or OLAP function.
I also tried with followiong ways :
SELECT (ROW_NUMBER() OVER(ORDER BY LOCATION)) AS RN ,* FROM CSPAPP.LOCATIONSWHERE RN < 200SELECT (ROW_NUMBER() OVER(ORDER BY LOCATION)) AS RN ,LOCATION FROM CSPAPP.LOCATIONSWHERE RN < 200Best Answer
You can't reference an alias on the same level where it is defined. You need to wrap this into a derived table:
SELECT locationFROM (SELECT row_number() over(order by location) as rn, location FROM cspapp.locations) WHERE rn < 200I use something like this when selecting based on row number in iSeries DB2:
SELECT *FROM (SELECT ROW_NUMBER() OVER(ORDER BY location) as RRN, *FROM CSPAPP.LOCATIONS)WHERE RRN between 100 and 200If you are only interested in the 1 field you may be able to assign a name to the select and reference the fields:
SELECT DATA.locationFROM (SELECT ROW_NUMBER() OVER(ORDER BY location) as RRN, *FROM CSPAPP.LOCATIONS) as DATAWHERE DATA.RRN between 100 and 200You could try FETCH FIRST 200 ROWS ONLY instead of row_number. Write your select as you would normally without ROW_NUMBER, order by whatever you need and FETCH FIRST x.
selecting all columns with "*" is not a good practice especially if you have 600+ columns (and that by itself is bad design of the database).
Without using 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;You can reference an alias on the same level where it is defined. You need to wrap this into a derived table:
SELECT T1.* FROM(SELECT row_number() over(order by location) as rn ,L.* FROM cspapp.locations L) As T1 WHERE T1.rn < 200But you should understand * is never be a best practice. You should use the column name rather than * (L.col1).