> I've looked at it again and I think the code is correct, Good news! I will look into your explanation.
> but I > miswrote that the array needs to be sorted. The above query returns: > x | y | nth_value > ---+---+----------- > 1 | 1 | 2 > 2 | 2 | 1 > 3 | | 2 > 4 | 4 | > 5 | | 4 > 6 | 6 | 7 > 7 | 7 | 6 > (7 rows) > > This is correct, for values of x: > > 1: The first non-null value of y is at position 0, however we have > EXCLUDE CURRENT ROW so it picks the next non-null value at position 1 > and stores it in the array, returning 2. > 2: We can now take the first non-null value of y at position 0 and > store it in the array, returning 1. > 3. We take 1 preceding, using the position stored in the array, returning 2. > 4. 1 preceding and 1 following are both null, and we exclude the > current row, so returning null. > 5. 1 preceding is at position 3, store it in the array, returning 4. > 6. 1 preceding is null and we exclude the current row, so store > position 6 in the array, returning 7. > 7. 1 preceding is at position 5, store it in the array and return 6. > > It will be unordered when the EXCLUDE clause is used but the code > should handle this correctly. Best reagards, -- Tatsuo Ishii SRA OSS K.K. English: http://www.sraoss.co.jp/index_en/ Japanese:http://www.sraoss.co.jp
