Many relational database management systems (RDBMSs) provide a DENSE_RANK() function that we can use in our SQL queries. The SQL DENSE_RANK() function is a window function that returns the rank of the current row within its partition, without gaps.

The “without gaps” part is what distinguishes the DENSE_RANK() function from the RANK() function.

DENSE_RANK() returns contiguous rank numbers whenever there are ties, whereas RANK() will leave a gap between the tie and the next rank, resulting in noncontiguous rank numbers.

Continue reading →

In SQL databases, the RANK() function is a window function that returns the rank of the current row within its partition, with gaps.

By “with gaps” I mean that if there are any ties for a given rank, there will be a gap between that rank value and the next rank value.

If you don’t want such gaps, use the DENSE_RANK() function instead, as it returns the rank without gaps.

Continue reading →

Many relational database management systems (RDBMSs) provide us with a range of window functions.

The CUME_DIST() function is a window function that’s commonly implemented in RDBMSs for the purpose of calculating the cumulative distribution across a data set. In other words, it calculates the relative position of a specified value in a group of values.

Continue reading →