When we have a query that returns a column with numerical data such as prices or population, we can use the following methods to automatically calculate the totals and subtotals within a category.
Continue reading4 Functions that Return Information about JSON Values in MySQL
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 readingOverview of the ROW_NUMBER() Function in MySQL
In 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.
Divide a Partition into Buckets with the NTILE() Function in MySQL
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.
Overview of the LEAD() Function in MySQL
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 readingOverview of the LAG() Function in MySQL
In 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 readingUnderstanding the NTH_VALUE() Function in MySQL
In 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.
Introduction to the LAST_VALUE() Function in MySQL
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.
Introduction to the FIRST_VALUE() Function in MySQL
In MySQL, the FIRST_VALUE() function is a window function that returns the value of the given expression from the first row of the window frame.
Understanding the DENSE_RANK() Function in MySQL
In MySQL, the DENSE_RANK() function is a window function that returns the rank of the current row within its partition, without gaps.
By “without gaps” this means that it returns contiguous rank numbers whenever there are peers. Peers are considered ties and receive the same rank. In such cases, the next rank value is one greater than the current one (i.e. the one that the peers receive).
Continue reading