Chen Hirsh reminds us of an important fact:
One of the scariest things that can happen when you develop SQL code is getting indeterminate results. When you run your query multiple times and each time gets a different value.
How can that happen?
SQL functions or queries can be categorized into two groups – deterministic vs. non-deterministic.
I even have a demo where I show off the non-deterministic nature of ROW_NUMBER() versus, say, RANK() or DENSE_RANK(). But there’s nothing too scary about it: just ensure that your PARTITION BY criteria are guaranteed to be unique and you’re good to go. H/T Madeira Data Solutions blog.