Fix Error “The function ‘ROW_NUMBER’ must have an OVER clause” in SQL Server

If you’re getting an error that reads “The function ‘ROW_NUMBER’ must have an OVER clause” in SQL Server, it’s probably because you’re calling the ROW_NUMBER() function without an OVER clause.

Window functions such as ROW_NUMBER() require an OVER clause (and that clause must have an ORDER BY clause).

To fix this issue, add an OVER clause when calling the ROW_NUMBER() function.

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

Fix Error “The function ‘DENSE_RANK’ must have an OVER clause with ORDER BY” in SQL Server

If you’re getting an error that reads “The function ‘DENSE_RANK’ must have an OVER clause with ORDER BY” in SQL Server, it’s probably because you’re calling the DENSE_RANK() function without an ORDER BY clause.

Window functions such as DENSE_RANK() require an OVER clause, and that clause must have an ORDER BY clause. If you’re getting the above error, it’s likely that you’re providing an OVER clause, but you’re omitting the ORDER BY clause.

To fix this error, add an ORDER BY clause to the OVER clause.

Continue reading

2 Possible Reasons You’re Getting “function nth_value(numeric) does not exist” in PostgreSQL

There are at least a couple of reasons you might get an error that reads “function nth_value(numeric) does not exist” in PostgreSQL.

It could be that you’re calling the nth_value() function without the correct number of arguments. It could also be that you’re passing the wrong argument type.

To fix this issue, be sure to pass the correct number of arguments, with the correct type.

Continue reading