Understanding the LAST_VALUE() Function in SQL

In SQL databases, LAST_VALUE() is a window function that returns the last value in an ordered set of values. It allows us to get a value from the last row of a query result set or partition.

You may need to explicitly set the window frame if you want LAST_VALUE() to return the actual last value from the partition or result set. That’s because in many/most DBMSs, the default window frame ends with the current row.

Continue reading

Understanding the DENSE_RANK() Function in SQL

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

Understanding the RANK() Function in SQL

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

Understanding the CUME_DIST() Function in SQL

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