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.

Continue reading

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.

Continue reading

Fix “‘GENERATE_SERIES’ is not a recognized built-in function name” in SQL Server

If you’re getting an error that reads “‘GENERATE_SERIES’ is not a recognized built-in function name” in SQL Server, it could be that you’re calling GENERATE_SERIES() in the wrong context.

In SQL Server, GENERATE_SERIES() is a relational operator that returns a series of values from a given start and stop point.

When we call GENERATE_SERIES() in a SQL query, we don’t include it in the SELECT list, like we would with many SQL functions. Instead, we reference it in the FROM clause, like we would reference a table.

So if you’re getting the above error, it could be that you’re inadvertently making this mistake. To fix, be sure to call GENERATE_SERIES() in the correct context.

Continue reading

How to use VALUES as a Stand Alone SQL Statement in PostgreSQL

Many of us who work with SQL databases have used the VALUES keyword with the INSERT statement when inserting data into a database.

But perhaps a lesser known fact is that some RDBMSs allow us to use VALUES as a stand alone SQL statement. PostgreSQL is one such RDBMS.

When used as a stand alone statement, we can use a VALUES statement instead of a SELECT statement.

Continue reading

Understanding the Right Shift and Left Shift Operators in SQL Server

SQL Server 2022 introduced a bunch of new bit manipulation functions and operators. Amongst these are the right shift and left shift operators.

The right shift and left shift operators can be used to shift bits to the left or right. Given an integer or binary expression, these operators can shift bits by the amount that we specify.

Continue reading

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

If you’re getting error message 4112 that reads “The function ‘CUME_DIST’ must have an OVER clause with ORDER BY” when using the CUME_DIST() function, it’s probably because you’re omitting the ORDER BY clause from the OVER clause.

When using the CUME_DIST() function in SQL Server, we must include an OVER clause that contains an ORDER BY clause. This error happens when we provide the OVER clause but not the ORDER BY clause.

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

Continue reading

Using VALUES as a Stand Alone Statement in MySQL

MySQL 8.0.19 introduced the VALUES DML statement, which is a table constructor that can be used as a stand alone SQL statement.

This is not to be confused with the VALUES keyword that’s used with the INSERT or REPLACE statements when inserting data (although it can be used for that purpose too). It also shouldn’t be confused with the VALUES() function that’s used with INSERT … ON DUPLICATE KEY UPDATE statements.

Continue reading

Fix “Window element in OVER clause can not also be specified in WINDOW clause” in SQL Server

If you’re getting error number 4123 that reads “Window element in OVER clause can not also be specified in WINDOW clause” in SQL Server, it’s probably because you’re referring to a named window with the same clause that’s in the named window.

When we use the OVER clause to refer to a named window in SQL Server, we can’t include a clause that’s also present in the named window.

To fix the issue, remove one of the clauses – either the one in the OVER clause or the one in the named window.

Continue reading