CODEWITHSUNDEEP
sqldb2
Mandar
Mandar

Reputation: 379

ROW_NUMBER() in DB2

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

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

It 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.LOCATIONS
WHERE RN < 200

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

Upvotes: 13

Views: 83623

Answers (5)

Md. Kamruzzaman
Md. Kamruzzaman

Reputation: 1905

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;

Upvotes: 2

Sinisa Hajnal
Sinisa Hajnal

Reputation: 184

You 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).

Upvotes: 3

Ravindra Kumar
Ravindra Kumar

Reputation: 59

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

But you should understand * is never be a best practice. You should use the column name rather than * (L.col1).

Upvotes: -1

clutton
clutton

Reputation: 640

I 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 200

If 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.location
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY location) as RRN, *
    FROM CSPAPP.LOCATIONS
    ) as DATA
WHERE DATA.RRN between 100 and 200

Upvotes: 6

user330315
user330315

Reputation:

You can't reference an alias on the same level where it is defined. You need to wrap this into a derived table:

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

Upvotes: 10

Related Questions