Find 2nd highest salary in each dept using Postgres SQL and Spark SQL with Pyspark and Scala

rank() : function assigns a unique rank to each distinct value in the ordered partition. If there are ties (i.e., rows with the same value), the rank() function leaves gaps in the ranks.

dense_rank(): The dense_rank() function also assigns a unique rank to each distinct value in the ordered partition. However, unlike rank(), dense_rank() does not leave gaps in the ranks, even if there are ties. It assigns consecutive ranks to tied rows.

Conclusion: dense_rank() yields accurate results over rank().