Author: Ian

An Introduction to Invisible Columns in MySQL

Posted on by Ian

In MySQL 8.0.23 and later, we have the ability to create invisible columns when creating or altering a table.

An invisible column is one that is hidden to certain SQL queries, but can be accessed if explicitly referenced.

Basically, invisible columns are hidden to queries that use the asterisk wildcard (*) in their SELECT list. But they can be accessed by explicitly referencing the column by its name.

Invisible tables are hidden from the TABLE statement, as this statement provides no way of selecting individual columns.

Continue reading →

Fix Error “The function ‘RANK’ must have an OVER clause” in SQL Server

Posted on by Ian

If you’re getting an error that reads “The function ‘RANK’ must have an OVER clause” in SQL Server, it’s probably because you’re calling the RANK() function without an OVER clause.

The RANK() function requires an OVER clause (and that clause must have an ORDER BY clause).

To fix this issue, add an OVER clause when calling the RANK() function.

Continue reading →

2 Ways to Rename the Columns Returned by the TABLE Statement in MySQL

Posted on by Ian

MySQL has the handy TABLE statement that allows us to get the contents of a table with less code than what we’d need if we’d used the SELECT statement.

When we use the TABLE statement, it returns the columns using the actual column names. In most cases this is fine, and there’s no need to change anything. But sometimes we might want to rename one or more columns. In such cases, we can use the following techniques to achieve that outcome.

Continue reading →

Fix “The used SELECT statements have a different number of columns” in MySQL

Posted on by Ian

If you’re getting an error that reads “The used SELECT statements have a different number of columns” in MySQL, it’s probably because you’re using the EXCEPT, INTERSECT, or UNION clause, but with a different number of columns for each query.

When we use these clauses, both queries must select the same number of columns. For example, if the first query has two columns in its SELECT list, then the second query must also have two columns in its SELECT list.

To fix, make sure both queries select the same number of columns.

Continue reading →

How to Fix “The function ‘NTILE’ must have an OVER clause with ORDER BY” Error in SQL Server

Posted on by Ian

When using the NTILE() function in SQL Server, we must include an OVER clause clause with an ORDER BY clause.

If you’re getting error msg 4112 that reads “The function ‘NTILE’ must have an OVER clause with ORDER BY” when using the NTILE() function, it’s because, although you’re (correctly) including an OVER clause, you’re omitting the ORDER BY clause.

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

Continue reading →

Fix ‘Parse error: near “LIMIT”‘ in SQLite When Using the VALUES Statement

Posted on by Ian

If you’re getting a SQLite error that reads ‘Parse error: near “LIMIT”‘, it could be that you’re trying to use the LIMIT clause when using the VALUES clause as a stand alone statement.

SQLite doesn’t allow us to apply the LIMIT clause against the VALUES statement.

However, there is a work around. Below is an example of how we can apply the LIMIT clause against the VALUES statement.

Continue reading →

Fix “Invalid object name ‘GENERATE_SERIES'” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 208 that reads “Invalid object name ‘GENERATE_SERIES’“, it could be that you’re calling GENERATE_SERIES() in a version of SQL Server that doesn’t support this function.

The GENERATE_SERIES() function was introduced in SQL Server 2022 (16.x), and so if we try to call it in an earlier version of SQL Server, we’ll get the above error.

So if you’re running this on an earlier version, you’ll need to upgrade before you can run it successfully. You’ll also need to run it on a database with a compatibility level of at least 160.

Continue reading →