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).
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.
Understanding the DISTINCT ON Option in PostgreSQL
Most major RDBMSs support the DISTINCT clause, which allows us to get unique – or “distinct” – rows from our SQL queries. But PostgreSQL’s implementation of this clause has an extra option that most other RDBMSs don’t include.
PostgreSQL allows us to include the ON() option when using DISTINCT. This enables us to specify exactly which columns should be evaluated by the DISTINCT modifier, while simultaneously allowing us to return columns that aren’t evaluated.
Fix “SELECT DISTINCT ON expressions must match initial ORDER BY expressions” in PostgreSQL
If you’re getting a PostgreSQL error that reads “SELECT DISTINCT ON expressions must match initial ORDER BY expressions” when trying to run a query, it’s probably because the initial columns provided to your ORDER BY clause are different to the ones provided to the DISTINCT ON clause.
To fix this error, make sure the initial columns provided to the ORDER BY clause are included in the DISTINCT ON clause.
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 reading3 Oracle LIMIT Clause Alternatives
Many RDBMSs have a LIMIT clause that allows us to limit the rows returned by a query to a specified number. SQL Server doesn’t have a LIMIT clause but it does have a TOP() function that allows us to do the same thing.
But Oracle Database has neither.
Fortunately, Oracle does have a number of options that allow us to get the same result.
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.
How to Fix the Error “The function ‘FIRST_VALUE’ must have an OVER clause with ORDER BY” in SQL Server
If you’re getting error message 4112 that reads “The function ‘FIRST_VALUE’ must have an OVER clause with ORDER BY” when using the FIRST_VALUE() function, it’s probably because you’re omitting the ORDER BY clause from the OVER clause.
In SQL Server, the FIRST_VALUE() function requires an OVER clause that contains an ORDER BY clause. This error happens when we provide the OVER clause but not the ORDER BY clause.
To fix this error, simply add an ORDER BY clause to the OVER clause.
How to Use the DISTINCT Clause in SQL
Most of the major relational database management systems (RDBMSs) allow for a DISTINCT clause to be included in our SQL queries.
We use the DISTINCT keyword to return only unique rows. It eliminates duplicates from the results. If we have two or more rows with exactly the same data, we’ll only see one row in the results.