Context and Approach:
I was working on SQL Leetcode #176: Second Highest Salary and I implemented it via a Subquery using the ranking window function. I thought this would be the best approach because I could theoretically find the n-th ranking by simply changing my WHERE condition.
I was wondering if anyone understand the differences in runtime between these solutions and why it is so
The image above is where I use DENSE_RANK() because there is a corner case where there are more than one (n-1) higher ranks. This is the second fastest solution
In this version, SELECT DISTINCT is used with plain-old RANK(). It is the slowest.
This query combines SELECT DISTINCT As well as DENSE_RANK(). It is the fastest.