If you’re getting an error message that reads “The function ‘LAST_VALUE’ must have an OVER clause with ORDER BY.” when using the LAST_VALUE() function in SQL Server, it’s probably because you’ve omitted the ORDER BY clause from the OVER clause.
The LAST_VALUE() function requires an OVER clause that contains an ORDER BY clause. This error happens when we include the OVER clause but not the ORDER BY clause.
To fix this error, add an ORDER BY clause to the OVER clause.
Sometimes when we run a SQL query, we only want to see unique rows. But depending on the columns we’re selecting, we might end up with duplicate rows. And this could happen without even knowing it, especially with large data sets.
But it doesn’t have to be this way.
Fortunately most SQL databases provide us with an easy way to remove duplicates.
Continue readingIf you’re getting SQL Server error msg 402 that tells you the “data types” are “incompatible in the approx_percentile_cont operator“, it’s probably because you’re trying to apply the APPROX_PERCENTILE_CONT() function against a non-numeric column (or one that doesn’t evaluate to a numeric type).
When using the APPROX_PERCENTILE_CONT() function, the ORDER BY expression in the WITHIN GROUP clause must evaluate to an exact or approximate numeric type. Other data types are not allowed, and will result in the above error.
To fix this issue, be sure to apply the function against a numeric column/expression.
Continue readingIf you’re getting SQL Server error 10754 that reads “The function ‘APPROX_PERCENTILE_CONT’ must have a WITHIN GROUP clause” it’s probably because you’re calling the APPROX_PERCENTILE_CONT() function, but you’ve omitted the WITHIN GROUP clause.
To fix this issue, add a WITHIN GROUP clause to the function (and make sure it has an ORDER BY clause).
If you’re getting SQL Server error 10753 that reads “The function ‘LAST_VALUE’ must have an OVER clause”, it’s probably because you’re calling the LAST_VALUE() function without an OVER clause.
The LAST_VALUE() function requires an OVER clause (and that clause must have an ORDER BY clause).
To fix this issue, include an OVER clause when calling the LAST_VALUE() function.
If you’re getting an error that reads “Incorrect syntax near the keyword ‘DISTINCT’” when using the DISTINCT clause in SQL Server, it could be that you’ve put the DISTINCT clause in the wrong position.
When using the DISTINCT clause, it must be the first item in the SELECT list.
Therefore, to fix this error, check the position of the DISTINCT keyword. If it’s not the first item in the SELECT list, move it to the front so that it is the first item in the SELECT list.
The SQL UNION operator concatenates the results of two queries into a single result set. By default it returns distinct rows (i.e. it removes any redundant duplicate rows from the result set). But we can also use UNION ALL to return non-distinct rows (i.e. retain duplicates).
When it comes to NULL values, it’s pretty straight forward. SQL treats two NULL values as non distinct values. In other words, they’re duplicates.
Continue readingSuppose you have a query that returns multiple date columns, and suppose you want to return the latest date, regardless of which column it came from.
As from SQL Server 2022 we can use the GREATEST() function to easily achieve this outcome.
When using window functions such as RANK() in SQL Server, we must provide an OVER clause clause with an ORDER BY clause.
If you’re getting an error that reads “The function ‘RANK’ must have an OVER clause with ORDER BY”, it’s probably because you’re including an OVER clause with the RANK() function (as is required), but you’re omitting the ORDER BY clause.
To fix this error, add an ORDER BY clause to the OVER clause.
If you’ve ever used the UNION clause in a SQL query, chances are you used UNION ALL or just UNION by itself. But some RDBMSs also accept a UNION DISTINCT option.
The UNION DISTINCT option is basically the equivalent of UNION by itself. That is, it removes redundant duplicate rows.