public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: David G. Johnston <[email protected]>
Cc: Guyren Howe <[email protected]>
Cc: Erik Wienhold <[email protected]>
Cc: PG-General Mailing List <[email protected]>
Subject: Re: nth_value out of more than n values returns null
Date: Mon, 04 Nov 2024 19:49:52 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwZH2C=itB7h=S+0UiG5k=U4PsdaLKxsWenZ7GWFdAsDxA@mail.gmail.com>
References: <3ed0759d-c332-4f96-a147-499a694e9204@Spark>
<[email protected]>
<4e60ede0-86a5-4900-b415-05d68ad75cb1@Spark>
<CAKFQuwZH2C=itB7h=S+0UiG5k=U4PsdaLKxsWenZ7GWFdAsDxA@mail.gmail.com>
"David G. Johnston" <[email protected]> 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
view thread (6+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: nth_value out of more than n values returns null
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox