Commented by: @livius2

Hi,

this ticket have resolution "Fixed" that mean that NTH_VALUE work?
I see that it not working as expected

CREATE TABLE TEST
(
NAME varchar(20),
D date,
VAL integer,
POINTS integer
);

CREATE INDEX IDX_TEST1 ON TEST (NAME);

INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('KARLOS', '01.11.2014', '700', '8900');
INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('KARLOS', '02.11.2014', '800', '9000');
INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('KARLOS', '05.11.2014', '600', '8600');
INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('KARLOS', '08.11.2014', '900', '9200');
INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('MARTINES', '01.11.2014', '1200', '3000');
INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('MARTINES', '02.11.2014', '1300', '3100');
INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('MARTINES', '05.11.2014', '1400', '3400');
INSERT INTO TEST (NAME, D, VAL, POINTS) VALUES ('MARTINES', '08.11.2014', '1300', '3300');

SELECT
http://T.NAME, T.VAL, T.D
,(SELECT FIRST 1 T2.VAL FROM TEST T2 WHERE http://T2.NAME=T.NAME ORDER BY T2.D ASC) AS "FIRST"
,FIRST_VALUE(T.VAL) OVER(PARTITION BY http://T.NAME ORDER BY T.D ASC) AS "WFIRST"
,NTH_VALUE(T.VAL, 1) OVER(PARTITION BY http://T.NAME ORDER BY T.D ASC) AS "WNFIRST"

,\(SELECT FIRST 1 SKIP 1 T2\.VAL FROM TEST T2 WHERE <http://T2.NAME=T.NAME> ORDER BY T2\.D ASC\) AS "SECOND"
,NTH\_VALUE\(T\.VAL, 2\) OVER\(PARTITION BY <http://T.NAME> ORDER BY T\.D ASC\) AS "WNSECOND"

,\(SELECT FIRST 1 SKIP 2 T2\.VAL FROM TEST T2 WHERE <http://T2.NAME=T.NAME> ORDER BY T2\.D ASC\) AS "THIRD"
,NTH\_VALUE\(T\.VAL, 3\) OVER\(PARTITION BY <http://T.NAME> ORDER BY T\.D ASC\) AS "WNTHIRD"

,\(SELECT FIRST 1 SKIP 3 T2\.VAL FROM TEST T2 WHERE <http://T2.NAME=T.NAME> ORDER BY T2\.D ASC\) AS "FOURTH"
,NTH\_VALUE\(T\.VAL, 4\) OVER\(PARTITION BY <http://T.NAME> ORDER BY T\.D ASC\) AS "WNFOURTH"

,\(SELECT FIRST 1 SKIP 4 T2\.VAL FROM TEST T2 WHERE <http://T2.NAME=T.NAME> ORDER BY T2\.D ASC\) AS "FIFTH"
,NTH\_VALUE\(T\.VAL, 5\) OVER\(PARTITION BY <http://T.NAME> ORDER BY T\.D ASC\) AS "WNFIFTH"

FROM
TEST T

ORDER BY http://T.NAME, T.D ASC

result

NAME VAL D FIRST WFIRST WNFIRST SECOND WNSECOND THIRD WNTHIRD FOURTH WNFOURTH FIFTH WNFIFTH
1.KARLOS 700 01.11.2014 700 700 700 800 NULL 600 NULL 900 NULL NULL NULL
2.KARLOS 800 02.11.2014 700 700 700 800 800 600 NULL 900 NULL NULL NULL
3.KARLOS 600 05.11.2014 700 700 700 800 800 600 600 900 NULL NULL NULL
4.KARLOS 900 08.11.2014 700 700 700 800 800 600 600 900 900 NULL NULL
5.MARTINES 1200 01.11.2014 1200 1200 1200 1300 NULL 1400 NULL 1300 NULL NULL NULL
6.MARTINES 1300 02.11.2014 1200 1200 1200 1300 1300 1400 NULL 1300 NULL NULL NULL
7.MARTINES 1400 05.11.2014 1200 1200 1200 1300 1300 1400 1400 1300 NULL NULL NULL
8.MARTINES 1300 08.11.2014 1200 1200 1200 1300 1300 1400 1400 1300 1300 NULL NULL

as you can see WNSECOND for first row is wrong NULL
WNTHIRD for row 1 and 2 is wrong NULL

This is most wanted functionality
Whole Window functions make FB better then ever

Commented by: @asfernandes

Did you read this section of the documentation?

It's important to note that FIRST_VALUE, LAST_VALUE and NTH_VALUE also operates on a window frame,
and Firebird is currently always framing from the first to the current (and not the last) row of
the partition. This is likely to get strange results for NTH_VALUE and specially LAST_VALUE.

Commented by: @livius2

Reallt? no.

i follow your subtasks and when i see that somethink is done "fixed" i test this.
This task should remain open do you think? It is not done.

ps. thanks for great work :)

Commented by: @asfernandes

What is not done and why it's not done?

Because it don't work as you wish does not mean so.

Did you tested another RDBMS? It should behave like Firebird.

I really don't tested your comments, I'm just guessing you're talking about the "framing thing" as asked you to confirm.

The main task already has a subticket for frame: CORE3647. It will not be implemented in this version.

Commented by: @livius2

Now i see - we can get only previous row from already fetched resultset
not nth_value from ordered partition
and because of that i got Null for nth_value(2) for first row - because second row is not already fetched ..

When do you think think can this be supported Q2 2015 ? :) Or this can take much more time?