At the risk of being tedious, I'll point out that your use of the analytic function assumes a particular default in the SQL dialect. For your example to be unambiguous you would need to add "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW".
While this can seem to be nitpicking, I think it tells us all that we can easily mistakenly think we know a feature of the SQL language when in fact we just know a feature of some implementations. i.e. the implementation is applying a default ROWS clause whether we realise it or not.
On another point, I have found a main reason for not using analytic functions occurs when they're simply not there in the dialect. You might be surprised how many of these are around in all sorts of products.
You've done a good job here of describing alternatives, I expect someone somewhere sometime will find themselves needing that option and will be grateful for your examples.
That is why I provided multiple ways to perform the action! Not just using an analytic function. Analytic functions just tend to be the quickest and easiest to understand.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
At the risk of being tedious, I'll point out that your use of the analytic function assumes a particular default in the SQL dialect. For your example to be unambiguous you would need to add "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW".
I've covered this previously in a similar response on dev.to - see dev.to/helenanders26/sql-301-why-y...
While this can seem to be nitpicking, I think it tells us all that we can easily mistakenly think we know a feature of the SQL language when in fact we just know a feature of some implementations. i.e. the implementation is applying a default ROWS clause whether we realise it or not.
On another point, I have found a main reason for not using analytic functions occurs when they're simply not there in the dialect. You might be surprised how many of these are around in all sorts of products.
You've done a good job here of describing alternatives, I expect someone somewhere sometime will find themselves needing that option and will be grateful for your examples.
It's very true!
That is why I provided multiple ways to perform the action! Not just using an analytic function. Analytic functions just tend to be the quickest and easiest to understand.