Re: nth_value out of more than n values returns null

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> So just use “offset 5_000_000 limit 1”. Bringing in a window function here
> seems unhelpful.

Yeah, that. A bite-size example might help clarify what the window
function is doing:

regression=# create table zed(f1 int) ;
CREATE TABLE
regression=# insert into zed select generate_series(1, 10);
INSERT 0 10
regression=# select f1, nth_value(f1, 5) over (order by f1) from zed;
f1 | nth_value
----+-----------
1 |
2 |
3 |
4 |
5 | 5
6 | 5
7 | 5
8 | 5
9 | 5
10 | 5
(10 rows)

For the first four rows, the window frame doesn't include the row
you want, so you get NULL. You can fix that with a non-default
window frame:

regression=# select f1, nth_value(f1, 5) over (order by f1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from zed;
f1 | nth_value
----+-----------
1 | 5
2 | 5
3 | 5
4 | 5
5 | 5
6 | 5
7 | 5
8 | 5
9 | 5
10 | 5
(10 rows)

So yeah, you can get the fifth (or five million'th) row this way, but
you'll get N copies of it, which I assume is not what you want.
Better

regression=# select f1 from zed order by f1 offset 4 limit 1;
f1
----
5
(1 row)

which gets you just the one row and is a lot cheaper too.

regards, tom lane