PostgreSQL has a window function called dense_rank() that returns the rank of the current row, without gaps.
It works the same way that the rank() function works, but without gaps (the rank() function includes gaps).
Tag: how to
Using the RANK() Function to Add a “Rank” Column in PostgreSQL
PostgreSQL has a window function called rank() that returns the rank of the current row, with gaps.
“With gaps” means that it returns the same rank for any ties (i.e. two or more rows with the same value), but then subsequent ranks jump forward to account for the ties.
This means that there’s the potential for noncontiguous rank values. For example it could go 1, 2, 5, etc if several rows are ranked at 2. If there are no ties, then the rank values will be contiguous.
Continue readingAdd a Column of Row Numbers in PostgreSQL: The ROW_NUMBER() Function
In PostgreSQL, we can use the row_number() function to get each row’s number within its partition. This allows us to create a column with incrementing row numbers that reset with each new partition.
The row_number() function is a window function that’s specifically designed to return the number of the current row within its partition, starting at 1 and incrementing sequentially.
Fix ERROR 3581 “A window which depends on another cannot define partitioning” in MySQL
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.
2 Ways to Return Totals and Subtotals in SQL
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 readingCompute a Moving Average in SQL
SQL provides us with an easy way to automatically compute the moving average of a given column.
The moving average (also known as the rolling average, running average, moving mean (MM), or rolling mean) is a series of averages of different selections of the full data set. The moving average changes as we move through the data set. We can add the moving average to our SQL query result sets to see how it changes across the result set.
Continue readingFix ERROR 3579 “Window name ‘…’ is not defined” in MySQL
If you’re getting error 3579 in MySQL, which reads something like “Window name ‘wf3’ is not defined“, it’s probably because you’re referring to a named window that doesn’t exist.
To fix this issue, make sure you refer to a named window that exists.
Continue readingCreate a Running Total in SQL
We can use SQL to create a running total of a given column. By this I mean, we can create a column that calculates the cumulative sum of a given column.
The running total/cumulative sum is the sum of all rows up until the current row. The result is that the sum increases (or decreases in the case of negative values) with each row returned in the result set. Each row’s value is added to the cumulative amount from all prior rows, so for any given row, we get the total sum for all rows up to that point – the “running total”.
Continue readingFix ERROR 3580 “There is a circularity in the window dependency graph” in MySQL
If you’re getting error 3580 in MySQL, which reads “There is a circularity in the window dependency graph“, it’s probably because you’re using named windows that refer to each other in a way that creates a circular reference.
To fix this issue, make sure you don’t create a circular reference when referring to multiple named windows.
Continue reading