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.
Continue readingEver 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.
If you’re getting error 3581 in MySQL, which reads “A window which depends on another cannot define partitioning“, it’s probably because you’re using the PARTITION BY clause in a window function that refers to a named window.
To fix this issue, don’t use the PARTITION BY clause when referring to a named window.
MySQL provides us with a wide range of functions for working with JSON documents. Below are four MySQL functions that we can use to get information about JSON values.
Continue readingIn MySQL, ROW_NUMBER() is a window function that returns the number of the current row within its partition. Numbering starts at 1 and increments sequentially.
In MySQL, the NTILE() function is a window function that divides a partition into a given number of groups (buckets) and returns the bucket number of the current row within its partition.
In MySQL, the LEAD() function is a window function that returns the value of a given expression from the row that leads (follows) the current row by a given number of rows within its partition.
Basically, it returns the value from a later row.
Continue readingIn MySQL, the LAG() function is a window function that returns the value of a given expression from the row that lags (precedes) the current row by a given number of rows within its partition.
Basically, it returns the value from a previous row.
Continue readingIn MySQL, the NTH_VALUE() function is a window function that returns the value of a given expression from the from the N-th row of the window frame, where N is a number that we specify when calling the function.
In MySQL, the LAST_VALUE() function is a window function that returns the value of the given expression from the last row of the window frame.