How to Use the LAST_VALUE() Function in SQL Server

by

In SQL Server, the LAST_VALUE() function returns the last value in an ordered set of values.

LAST_VALUE() is a window function that enables us to get a value from the last row of a query result set or partition. This can be useful for when we want to do stuff such as compare a value from the current row with a value in the last row or include it in a calculation.

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 the default window frame ends with the current row. This is covered in the example below.

Read more

How to Use the FIRST_VALUE() Function in SQL Server

by

In SQL Server, FIRST_VALUE() is a window function that returns the first value in an ordered set of values.

Basically, we can use it to get a value from the first row of a query result set or partition. This can be handy if we want to compare a value in the current row with a value in the first row, or use it as part of a calculation.

Read more

Fix Error “function ntile() does not exist” in PostgreSQL

by

If you’re getting an error that reads “function ntile() does not exist” in PostgreSQL, it could be that you’re calling the ntile() function without any arguments.

This error message could be a bit misleading. The ntile() function does in fact exist (if you’re using PostgreSQL 8.4 or later), but the error occurs when we don’t provide an argument, or if we provide an argument of the wrong type.

Read more

Calculate the Difference Between the Current Row and a Following Row in MySQL

by

In MySQL, we can use the LEAD() function to get the value of a subsequent row. For example we can get a value from the next row, or the one after that, and so on.

This enables us to do things like compute the difference between a value in the current row and a value in a following row. We can do this even if both values are in the same column.

Read more

How to Fix Error “window function nth_value requires an OVER clause” in PostgreSQL

by

If you’re getting an error that reads “window function nth_value requires an OVER clause” in PostgreSQL, it’s because you’re calling the nth_value() function without an OVER clause.

PostgreSQL requires that you include an OVER clause any time you call a built in nonaggregate window function such as nth_value().

To fix this issue, add an OVER clause to your window function.

Read more

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

by

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

The CUME_DIST() function requires an OVER clause (and that clause must have an ORDER BY clause).

To fix this issue, be sure to include an OVER clause when calling the CUME_DIST() function.

Read more