Columns from mutiple tables in ROW_NUMBER() OVER (PARTITION BY .... ORDER BY ...)

Question

I am using the ROW_NUMBER function like this

SELECT ROW_NUMBER() OVER (PARTITION BY TABLE1.COL1, TABLE2.COL1 ORDER BY TABLE1.COL4) as ROWNUM
FROM TABLE1
JOIN TABLE2
ON ......

The data that I am running this query with returns only one row but the ROWNUM is not 1 as I expect, sometimes it is 4 and sometimes 5.

Is it wrong to use columns from mutiple tables in the PARTITION BY... ORDER BY..

Thanks in advance for the help

Answer 1

The partition by will restart the row count for each change in Table1.col1 and Table2.col1

It seems like you are expecting a result which would be achieved through the following change:

SELECT ROW_NUMBER() OVER (ORDER BY TABLE1.COL4) as ROWNUM
FROM TABLE1
JOIN TABLE2
ON ......