PostgreSQL provides a list of window functions that are operated on the set of rows. One of the most significant window functions that is usually used is the NTH_VALUE() function. This function is widely used to get a particular value at a specific location/position. The most important application of this function is getting the specified positioned data from ordered data and partitioned data.

For example, we can get the 3rd value from the candidate marks data ordered in descending order. If the data is partitioned by the genders i.e. Male and female, by getting the 3rd value we will get the data for the candidate securing the 3rd position in both the gender categories.

While working with the NTH_VALUE() function we usually encounter the “window function nth_value requires an OVER clause”. In the following topic, the reason for this error is discussed, and the way we can fix it is provided.

How to Resolve the “window function nth_value requires an OVER clause” Error in PostgreSQL?

This error arises when we are using the NTH_VALUE() function without querying an OVER statement. For most of the window functions and here specifically for the NTH_VALUE() function, the OVER clause is necessary for the proper execution.

Let’s have a look at the query we are trying to run to find the 3rd value from the table named “test_scores”:

SELECT *, NTH_VALUE (candidate_score,3) 
FROM test_scores;

The above query results in an error:

Now here we can see that we have not specified any OVER clause. This error can be resolved by adding an OVER clause. Let’s try to fix the issue using the OVER clause.

SELECT *, NTH_VALUE (candidate_score,3) 
 OVER (
 ORDER BY candidate_score DESC 
 RANGE BETWEEN UNBOUNDED PRECEDING AND 
 UNBOUNDED FOLLOWING
 ) AS third_position
 FROM test_scores;

We have added an OVER clause in which we are ordering the candidate scores in descending order. Now we will see if the NTH_VALUE() function is working fine or not. The output of the above query is:

We can see that the NTH_VALUE() function is running fine now. So this is how we resolve this error.

Conclusion

The error “window function nth_value requires an OVER clause” occurs when the OVER clause is not specified in the query. The NTH_VALUE() function and some other window functions require the OVER clause for their proper execution. In this article, we have fixed the “window function nth_value requires an OVER clause” for the NTH_VALUE() function by adding the OVER statement in the query.