When combining multiple query blocks in MySQL, we can use the INTERSECT clause to return just those rows that are common to both query blocks.
It’s a bit like the UNION clause, except that it excludes rows that aren’t present in both queries.
How to Fix “The function ‘NTILE’ must have an OVER clause with ORDER BY” Error in SQL Server
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.
2 Ways to Rename the Columns when using VALUES to Create a Constant Table in PostgreSQL
When we use the VALUES statement to create a constant table, PostgreSQL automatically names the columns column1, column2, etc. This saves us from having to provide names for the columns or from getting a blank column header.
But we also have the option of naming the columns ourselves.
Fix ‘Parse error: near “LIMIT”‘ in SQLite When Using the VALUES Statement
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.
Fix “Invalid object name ‘GENERATE_SERIES'” in SQL Server
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.
Fix “Parse error: all VALUES must have the same number of terms” in SQLite when using the VALUES Stand Alone Statement
If you’re getting an error that reads “Parse error: all VALUES must have the same number of terms” in SQLite when using the VALUES clause as a stand alone statement, it’s probably because you’re not providing the same number of columns in all rows.
When we use VALUES to create a constant table, we must provide the same number of columns in each row.
To fix this issue, be sure to provide the same number of columns across all rows.
How to Rename the Columns Returned by the VALUES Statement in SQLite
If you’ve ever used the VALUES clause as a stand alone statement, you may have noticed that SQLite provides default column names for the results. SQLite conveniently names them column1, column2, and so on.
However as convenient as this is, you might want to provide names that are more meaningful.
Fortunately there’s an easy way to do that.
Fix Error “The function ‘DENSE_RANK’ must have an OVER clause” in SQL Server
If you’re getting error 10753 when using a window function in SQL Server, it’s probably because you’re calling the function without an OVER clause.
When using the DENSE_RANK() function, the error message reads “The function ‘DENSE_RANK’ must have an OVER clause”.
The DENSE_RANK() function requires an OVER clause (and that clause must have an ORDER BY clause).
To fix this issue, add an OVER clause to the DENSE_RANK() function.
Fix SQLite ‘Parse error: near “ORDER”‘ When Using VALUES as a Stand Alone Statement
If you’re getting an error that reads ‘Parse error: near “ORDER”‘ in SQLite, it could be that you’re trying to use the ORDER BY clause when using the VALUES clause as a stand alone statement.
Although we can certainly use the VALUES clause as a stand alone SQL statement, we can’t apply the ORDER BY clause against it.
However all is not lost. Below is an example of how we can sort the results of the VALUES statement.
Using VALUES instead of SELECT in SQLite
Some database management systems, including SQLite, allow us to use the VALUES keyword as a stand alone SQL statement. So we can use VALUES to return a constant table, usually with less code than would be required if we were to use the SELECT statement.
The following examples demonstrate how.