Re: nth_value out of more than n values returns null

[Tom Lane <tgl@xxxxxxxxxxxxx>]

"David G. Johnston" <david.g.johnston@xxxxxxxxx> 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