In MySQL, list_add() is a system function that adds a value to a comma-separated list of values and returns the result.
So it’s a handy function that allows us to append a value to a list.
Continue readingOverview of the LIST_DROP() Function in MySQL
In MySQL, list_drop() is a system function that removes a value from a comma-separated list of values and returns the result.
MySQL ANY_VALUE() Function Explained
In MySQL, ANY_VALUE() is an aggregate function that allows us to include nonaggregated columns in the SELECT list when using the GROUP BY clause.
The ANY_VALUE() function is commonly used to resolve issues that arise when a query includes columns that are not part of the GROUP BY clause or aggregate functions. It can be useful in scenarios where we want to include descriptive columns in a grouped result set without affecting the grouping behaviour.
Fix “LIMIT is only supported in combination with either BYSCORE or BYLEX” in Redis
If you’re getting an error that reads “LIMIT is only supported in combination with either BYSCORE or BYLEX” it’s probably because you’re trying to use the LIMIT clause without using the BYSCORE or BYLEX arguments.
This can happen when using the VRANGE command without either of the BYSCORE or BYLEX arguments.
To fix this issue, use either the BYSCORE or BYLEX argument when using the command. This obviously means that we need to adjust our query so that it’s querying by score or lexicographically.
How to Create a Repeating Sequence in PostgreSQL
When we create a sequence in PostgreSQL, we have the option of making it a non-repeating sequence or repeating sequence.
By “repeating” sequence, I mean that, once it reaches its maximum value (or minimum if it’s a descending sequence), it starts all over again, re-using numbers previously generated.
Continue readingHow WITH ROLLUP Works in MySQL
MySQL provides us with a handy WITH ROLLUP modifier that allows us to get totals and subtotals when using the GROUP BY clause.
Below is an example of how the MySQL WITH ROLLUP modifier works.
Fix Error 6358 “…is not a valid style number when converting to XML” in SQL Server
If you’re getting error msg 6358 which reads something like 300 is not a valid style number when converting to XML, it’s probably because you’re trying to convert a value to XML, but the style that you’re specifying isn’t supported for conversions to that data type.
It’s not that the conversion can’t happen, it’s just that it can’t happen using the style that you’re specifying.
Continue reading10 Functions and Operators that Search JSON Documents in MySQL
MySQL provides us with a bunch of functions and operators that allow us to search JSON documents for various things, such as specific values, paths, keys, etc.
Here are seven functions and three operators that search JSON documents in MySQL.
Continue readingUnderstanding the VAR_SAMP() Function in MySQL
In MySQL, the VAR_SAMP() function returns the sample variance of an expression. The denominator is the number of rows minus one.
If there are no matching rows, or if the expression is NULL, VAR_SAMP() returns NULL.
VAR_SAMP() is an aggregate function, and so it can be used with the GROUP BY clause.
The VAR_SAMP() function can also be used as a window function.
How VARIANCE() Works in MySQL
In MySQL, the VARIANCE() function returns the population standard variance of an expression.
If there are no matching rows, or if the expression is NULL, the function returns NULL.
The VARIANCE() function is a synonym for the standard SQL VAR_POP() function. In other words, they both do the same thing, but VARIANCE() is not standard SQL. Therefore, if you need to use standard SQL, use VAR_POP() instead.