ROW_NUMBER Constraint Against a View-CodePudding

I'm testing a simple query section that runs from a view instead of a table. When I leave out the ROW_NUMBER line, the query returns results. With this line active, I just get NULL results. Is there some restriction against doing these kinds of operations on a view?

SELECT
    `VMC Savings`,
    ROW_NUMBER() OVER (ORDER BY `VMC Savings`) AS row_n
FROM v_vmc_summary

Result with ROW_NUMBER code:

Result with ROW_NUMBER commented out:

CodePudding user response：

without a partition by every record is evaluated and if null exists; Null will replace every value.

Partition by the complete key or filter out null results.

"Partition by clause is an optional part of Row_Number function and if you don't use it all the records of the result-set will be considered as a part of single record group or a single partition and then ranking functions are applied."

above source: https://www.c-sharpcorner.com/blogs/rownumber-function-with-partition-by-clause-in-sql-server1

but I believe it applies to mysql as well..

Since it looks at it as a single partition; and NULL exists and null needs special handling... I think there in is the problem.

I suppose you could coalesce(value,0) if you are willing the nulls to be 0.