In SQL Server, LAG() is a window function that enables us to access a value from a previous row in the same result set, without the need to perform a self-join.
We specify the previous row as an offset from the current row. An offset of 1 means the previous row, an offset of 2 means two rows back, and so on.
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.
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.
In SQL Server, PERCENT_RANK() is a window function that calculates and returns the relative rank of a row within a group of rows, expressed as a value between 0 and 1.
In other words, it returns the percentage of partition values less than the value in the current row, excluding the highest value.
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.
MySQL 8.0.17 introduced a couple of functions that allow us to validate JSON documents against their schemas. These are listed below, with examples.
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.
In SQL Server, the CUME_DIST() function calculates and returns the cumulative distribution of a value within a group of values. This is the relative position of a specified value in a group of values.
Below is a list of functions that can be used as window functions in MySQL.
Some are nonaggregate functions that can only be used as window functions, while others are aggregate functions that can be used as window functions if required.
Ever since the release of MySQL 8.0.16, we’ve had the ability to create CHECK constraints in MySQL. At some point, we may want to return a list of CHECK constraints that have been created in a database or against a given table.
Fortunately, we can use the information_schema.check_constraints view to do just that.
We can alternatively use the information_schema.table_constraints view to get the same info.