How to Fix Error 245 When Using LEAST() or GREATEST() in SQL Server

Posted on by Ian

If you’re getting error 245 that reads something like “Conversion failed when converting the varchar value ‘Five’ to data type int” when using the LEAST() or GREATEST() functions in SQL Server, it’s probably because your arguments aren’t of a comparable data type.

When using LEAST() and GREATEST(), all arguments must be of a data type that is comparable and that can be implicitly converted to the data type of the argument with the highest precedence.

To fix this issue, be sure to pass arguments of comparable data types.

Continue reading →

5 Ways to Calculate the Difference Between Values in the Same Column (But Different Rows) in MySQL

Posted on by Ian

MySQL has a bunch of functions that enable us to get values from another row in the same column. This makes it easy for us to do stuff like calculate the difference between a value in the current row and one in another row, even if it’s in the same column.

Here are five options for calculating the difference between a value in the current row and a value in a different row but in the same column.

Continue reading →

Understanding the DATE_BUCKET() Function in SQL Server

Posted on by Ian

The release of SQL Server 2022 came with the introduction of the DATE_BUCKET() function.

The DATE_BUCKET() function allows us to arrange data into groups that represent fixed intervals of time. It returns the date/time value that corresponds to the start of each date/time bucket, as defined by the arguments passed to the function.

Continue reading →

Fix SQL Server Error 189: “The greatest function requires 1 to 254 arguments”

Posted on by Ian

If you’re getting SQL Server error number 189 that reads “The greatest function requires 1 to 254 arguments“, it’s probably because you’re either passing too few or too many arguments.

As the error message alludes to, you need to pass at least 1 argument, and no more than 254 arguments when using the GREATEST() function.

To fix this issue, be sure to pass at least 1 argument, and no more than 254 arguments.

Continue reading →

Fixing the Error: “The function ‘LAST_VALUE’ must have an OVER clause with ORDER BY” in SQL Server

Posted on by Ian

If you’re getting an error message that reads “The function ‘LAST_VALUE’ must have an OVER clause with ORDER BY.” when using the LAST_VALUE() function in SQL Server, it’s probably because you’ve omitted the ORDER BY clause from the OVER clause.

The LAST_VALUE() function requires an OVER clause that contains an ORDER BY clause. This error happens when we include the OVER clause but not the ORDER BY clause.

To fix this error, add an ORDER BY clause to the OVER clause.

Continue reading →

3 Ways to Remove Duplicate Rows from Query Results in SQL

Posted on by Ian

Sometimes when we run a SQL query, we only want to see unique rows. But depending on the columns we’re selecting, we might end up with duplicate rows. And this could happen without even knowing it, especially with large data sets.

But it doesn’t have to be this way.

Fortunately most SQL databases provide us with an easy way to remove duplicates.

Continue reading →

Understanding the TRIM() Function in SQL Server

Posted on by Ian

In SQL Server, we can use the TRIM() function to remove leading and trailing characters from a given string.

A common use case is to remove whitespace from the start and end of the string, but we can also specify other characters to remove.

Also, as from SQL Server 2022, we can specify which side of the string to remove the characters from (i.e. leading, trailing, or both).

Continue reading →