CODEWITHSUNDEEP
sqldb2db2-400
RegisPierre
RegisPierre

Reputation: 3

Using ROW_NUMBER() OVER() in subquery

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?

Upvotes: 0

Views: 1242

Answers (2)

WarrenT
WarrenT

Reputation: 4542

In this case, you might want to consider whether to use RRN(T1) instead of ROW_NUMBER().

But as Mark B said, you should always use an ORDER BY clause with ROW_NUMBER() to prevent an unpredictable sequencing of rows returned. If there is no appropriate column to order by, you might ORDER BY RRN(T1).

Upvotes: 0

Mark Barinstein
Mark Barinstein

Reputation: 12314

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

Upvotes: 2

Related Questions