Database Management Systems
The release of SQL Server 2022 in November 2022 introduced a bunch of new functionality, including some enhancements to the TRIM(), LTRIM() and RTRIM() functions.
The enhancements in the LTRIM() and RTRIM() functions are different to those in the TRIM() function. Below is a quick overview of the enhancements to these functions, along with examples.
MySQL has a LAG() function that we can use to return the value of a previous row of our choosing, based on how many rows back it is from the current row.
Armed with that value, we can then calculate the difference between it and the current row’s value.
Continue readingIf you’re getting SQL Server error 10753 that reads “The function ‘NTILE’ must have an OVER clause”, it’s probably because you’re calling the NTILE() function without an OVER clause.
The NTILE() function requires an OVER clause (and that clause must have an ORDER BY clause).
To fix this issue, be sure to include an OVER clause when calling the NTILE() function.
When using the GROUP BY clause in SQL Server, we can use the ROLLUP modifier to create subtotals and grand totals, etc.
You may have seen two variations of this. One as GROUP BY ROLLUP () and the other as GROUP BY ... WITH ROLLUP.
You may be wondering which one you should use?
As it turns out, Microsoft recommends that we use the first syntax; GROUP BY ROLLUP (). The other syntax is provided for backward compatibility only.
In SQL Server, the GENERATE_SERIES() function is a relational operator that returns a series of values between a given start and stop point. These are returned in a single-column table.
Although the GENERATE_SERIES() function only works with numeric values, we can combine it with other functions to create a series of dates.
The GENERATE_SERIES() function was introduced in SQL Server 2022 (16.x) and requires the compatibility level to be at least 160.
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 readingIf you have a query that returns multiple columns with numeric values in SQL Server, you can pass them to the GREATEST() function to find out which column has the highest value.
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 readingThe 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.
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