Most RDBMSs have functions that make it easy to calculate the median value from a column in our queries.
Below are three SQL functions we can use to calculate the median value in a column.
Continue readingTag: how to
How to Fix Error “function mode() does not exist” in PostgreSQL
If you’re getting an error that reads “function mode() does not exist” in PostgreSQL, it could be that you’re using the wrong syntax with this function.
The mode() function requires a WITHIN GROUP clause, and we can get the above error if we remove that clause.
In this case, we can fix the error by adding a valid WITHIN GROUP clause.
Fix Error “The function ‘ROW_NUMBER’ must have an OVER clause” in SQL Server
If you’re getting an error that reads “The function ‘ROW_NUMBER’ must have an OVER clause” in SQL Server, it’s probably because you’re calling the ROW_NUMBER() function without an OVER clause.
Window functions such as ROW_NUMBER() require an OVER clause (and that clause must have an ORDER BY clause).
To fix this issue, add an OVER clause when calling the ROW_NUMBER() function.
Create a Named Window for a Window Function in SQL
When we create a window function in SQL, we have the option of defining it directly in the OVER clause or in a named window. When we define it in a named window, we can refer to that window name in our OVER clause, which saves us from having to define it directly in the OVER clause.
Fix Error “The function ‘DENSE_RANK’ must have an OVER clause with ORDER BY” in SQL Server
If you’re getting an error that reads “The function ‘DENSE_RANK’ must have an OVER clause with ORDER BY” in SQL Server, it’s probably because you’re calling the DENSE_RANK() function without an ORDER BY clause.
Window functions such as DENSE_RANK() require an OVER clause, and that clause must have an ORDER BY clause. If you’re getting the above error, it’s likely that you’re providing an OVER clause, but you’re omitting the ORDER BY clause.
To fix this error, add an ORDER BY clause to the OVER clause.
How to Skip Rows that Fail a CHECK Constraint in MySQL
Normally if we try to insert data that violates a CHECK constraint in MySQL, we get an error, and the whole INSERT operation fails (including any conforming rows).
But it’s possible to change this, so that any conforming rows are inserted, and only the non-conforming rows are skipped.
We can do this by using the IGNORE clause.
Using the LAST_VALUE() Function to Get the Value from the Last Row in PostgreSQL
In PostgreSQL the last_value() function returns the value from the last row of the current window frame.
2 Possible Reasons You’re Getting “function nth_value(numeric) does not exist” in PostgreSQL
There are at least a couple of reasons you might get an error that reads “function nth_value(numeric) does not exist” in PostgreSQL.
It could be that you’re calling the nth_value() function without the correct number of arguments. It could also be that you’re passing the wrong argument type.
To fix this issue, be sure to pass the correct number of arguments, with the correct type.
Continue readingUsing the FIRST_VALUE() Function to Get the Value from the First Row in PostgreSQL
In PostgreSQL the first_value() function returns the value from the first row in the current window frame.
We can use this function to get the value from the first row in a result set, or from the first row in the current partition, or some other window frame that’s been specified.
Continue readingFix Error “invalid input syntax for type integer” When using the NTILE() Function in PostgreSQL
If you’re getting an error that reads “ERROR: invalid input syntax for type integer” in PostgreSQL, it’s probably because you’re calling the ntile() function with an argument of the wrong data type.
This error can occur when we pass the wrong type to the ntile() function. We can get a different error depending on the type, but this error can occur when we pass a string.
To fix this issue, be sure to pass a positive integer to the function.
Continue reading