"David G. Johnston" <david.g.johnston@gmail.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)
fromzed;
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
