Windowing Function Patch Review -> NTH_VALUE - Mailing list pgsql-hackers

I'm having a little trouble understanding the standard for NTH_VALUE(). I
would have assumed that NTH_VALUE(name,1) would return the first name in the
window. The current patch is using 0 for the first.

Here is the paragraph I'm reading in the standard:

"The nth-value function takes an arbitrary <value expression> VE and a
<simple value specification> or a <dynamic parameter specification> that
evaluates to an exact numeric value n with scale 0 (zero) as arguments
and, for each row R of a windowed table, returns the value of VE evaluated
on the n-th row from the first (if FROM FIRST is specified or implied) or
the last (if FROM LAST is specified) row of the window frame of R defined by
a window structure descriptor. In addition, RESPECT NULLS or IGNORE NULLS
can be specified to indicate whether the rows for which VE evaluates to the
null value are preserved or eliminated."

The text "returns the value of VE evaluated on the n-th row from the first".
I find the "from the first" quite difficult to understand. If it said "in
the window partition" then that seems simple. I'm not sure if "from the
first" includes or does not include the first row in the window partition.

Perhaps it's easier to see in an example.

(Using employees table from another thread) for those who missed it:

create table employees ( id INT primary key, name varchar(30) not null, department varchar(30) not null, salary int not
null,check (salary >= 0) 
);


insert into employees values(1,'Jeff','IT',10000);
insert into employees values(2,'Sam','IT',12000);

insert into employees values(3,'Richard','Manager',30000);
insert into employees values(4,'Ian','Manager',20000);

insert into employees values(5,'John','IT',60000);
insert into employees values(6,'Matthew','Director',60000);



david=# select *,nth_value(name,1) over (order by id) from employees;id |  name   | department | salary | nth_value
----+---------+------------+--------+----------- 1 | Jeff    | IT         |  10000 | 2 | Sam     | IT         |  12000
|Sam 3 | Richard | Manager    |  30000 | Sam 4 | Ian     | Manager    |  20000 | Sam 5 | John    | IT         |  60000
|Sam 6 | Matthew | Director   |  60000 | Sam 
(6 rows)

"Sam" is the name from the 2nd row in the window partition.

david=# select *,nth_value(name,0) over (order by id) from employees;id |  name   | department | salary | nth_value
----+---------+------------+--------+----------- 1 | Jeff    | IT         |  10000 | Jeff 2 | Sam     | IT         |
12000| Jeff 3 | Richard | Manager    |  30000 | Jeff 4 | Ian     | Manager    |  20000 | Jeff 5 | John    | IT
| 60000 | Jeff 6 | Matthew | Director   |  60000 | Jeff 


Also does anyone think that a negative nth_value should be disallowed. The
standard does not seem to give any details on this.

david=# select *,nth_value(name,-1) over (order by id) from employees;id |  name   | department | salary | nth_value
----+---------+------------+--------+----------- 1 | Jeff    | IT         |  10000 | 2 | Sam     | IT         |  12000
|3 | Richard | Manager    |  30000 | 4 | Ian     | Manager    |  20000 | 5 | John    | IT         |  60000 | 6 |
Matthew| Director   |  60000 | 

I also cannot find another RDBMS that implements NTH_VALUE to see what they
do.

Does anyone know if one exists?

Anyone out there able to understand what the standard requires in this case?

It just seems strange to have NTH_VALUE(col,1) return the 2nd row when
functions like ROW_NUMBER() work with base 1 rather than base 0.

Any help or comments on this would be appreciated.

David.