I had an interesting conversation at work where someone questioned my use of DENSE_RANK and suggested I use LAST_VALUE. We had a good conversation about it, but ultimately agreed to disagree about the specific scenario. My code was accurate with no performance issues warranting a rewrite. My feeling was the person I was talking too was less familiar with DENSE_RANK and so favored LAST_VALUE. I get preference, I have my own, but I try to be aware of what's preference vs. what's technically superior.

I'm curious about people's thoughts on when to use LAST_VALUE() vs MAX() KEEP(DENSE_RANK LAST ORDER BY ). To my mind, both solve a similar problem. I lean towards DENSE_RANK largely out of preference due to the syntax being shorter (literally just less characters to type), and I also like that since DENSE_RANK is an aggregate function it outputs distinct results without needing to use the DISTINCT keyword. I haven't intentionally run tests comparing the two, but anecdotally I've never noticed a performance difference between the two when writing comparable queries.

Surprisingly I couldn't really find any detailed articles or discussions online comparing these two functions and I'm curious what thoughts are out there in the wild.