In SQL Server, the GREATEST() function returns the maximum value from a list of one or more expressions.
The GREATEST() function was introduced in SQL Server 2022 (along with the LEAST() function).
Tag: functions
Fix Error “argument of ntile must be greater than zero” in PostgreSQL
If you’re getting an error that reads “ERROR: argument of ntile must be greater than zero” in PostgreSQL, it’s probably because you’re calling the ntile() function with a non-positive integer.
This error occurs when we pass a non-positive integer to the ntile() function.
To fix this issue, be sure to pass a positive integer to the function.
Continue readingAn Overview of the LEAST() Function in SQL Server
SQL Server 2022 introduced the LEAST() and GREATEST() functions, which allow us to get the minimum or maximum value from a list of expressions.
The LEAST() function returns the minimum value from a list of one or more expressions.
5 Ways to Get a Value from a Different Row in the Same Column in MySQL
MySQL includes some nonaggregate window functions that allow us to get a value from a specific row. We can use such functions to do things like, compare the value in the specified row with the value in the current row, even if both values are in the same column.
Below are five functions that we can use to do this.
Continue readingA Quick Introduction to the NTILE() Function in SQL
Many SQL databases have a window function called NTILE() function that divides a rowset or partition into a given number of groups (buckets). The function typically returns the bucket number of the current row within its partition.
Understanding the LAG() Function in SQL
The LAG() function is a window function that’s available in many SQL databases. It 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.
In other words, the LAG() function returns a value from a previous row.
An Introduction to the LEAD() Function in SQL
Many relational database management systems (RDBMSs) have a LEAD() function that allows us to retrieve a value from a following row.
The SQL LEAD() function 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.
RANK() vs DENSE_RANK() in SQL: What’s the Difference?
Many RDBMSs include both a rank() and a dense_rank() function in their list of window function offerings. At first glance, these functions might appear to do the same thing. However, there’s one important difference between these functions, and you will definitely need to be aware of this difference when choosing which function to use.
Using the ROW_NUMBER() Function to get Row Numbers in SQL
The SQL ROW_NUMBER() function is a window function that assigns and returns a row number of each row in a query partition or result set. Numbering starts at 1 and increments sequentially.
This enables us to add a “row number” column to our queries.
Continue readingAn Introduction to the MEDIAN() Function in SQL
Some relational database management systems (RDBMSs) have a MEDIAN() function that calculates the median value within a range of values.
It returns the middle value or an interpolated value that would be the middle value once the values are sorted.
The SQL MEDIAN() function typically operates on numeric expressions, but depending on the RDBMS, may also operate on other data types, such as datetime expressions.
Depending on the RDBMS, the MEDIAN() function can be used as a window function, as an aggregate function, or as both.