sql - Using ROW_NUMBER() OVER() in subquery

Question

I'm encountering a problem, actually I'm using a query like this:

SELECT * 
FROM
    (SELECT 
         ROW_NUMBER() OVER() as VAL_RRN,
         T1.FIELD1 AS FIELD1,
         T1.FIELD2 AS FIELD2 
     FROM 
         MYTABLE1 AS T1) AS subquery 
WHERE 
    VAL_RRN >= X
FETCH FIRST 10 ROWS ONLY

This query is used to display 10 results at one time to the user, and the X variable is used with a scroll system to let the user navigate through the results.

This query works well (but I don't know if this is the best way to do it).

Anyway, what I actually need to do is a little different:

SELECT * 
FROM
    (SELECT 
         ROW_NUMBER() OVER() as VAL_RRN,
         T1.FIELD1 AS FIELD1,
         T1.FIELD2 AS FIELD2,
         COALESCE((SELECT '1'
                   FROM MYTABLE1 AS T2 
                   WHERE T1.FIELD1 = T2.FIELD2 FETCH FIRST ROW ONLY), '0') AS FIELD3 FROM MYTABLE1 AS T1) AS subquery 
WHERE 
     VAL_RRN >= X
FETCH FIRST 10 ROWS ONLY

The COALESCE clause is used to know if one row has others rows referencing it.

In MYTABLE1 the FIELD1 is the primary key and the FIELD2 is a Foreign Key defined with:

CONSTRAINT CONSTRAINT_NAME FOREIGN KEY                            
  (FIELD2) REFERENCES MYTABLE (FIELD1) ON DELETE SET NULL

the MYTABLE table can have row which references other rows.

But when I'm using the second type of query, the actual value of row_number() over() is completely random, and I don't know why. I have tried to use VALUE() instead of COALESCE() but this doesn't seem to work, any ideas?

标签： sqldb2db2-400

Answer 1

You should use row_number() over(order by ...).
Row enumeration is unpredictable otherwise.