From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com>
cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: LAST_VALUE returns the entire partition
In-reply-to: 
 <CAPL5KHp2AfXb_xi+-gpffaS1-idNarvxFuUOqkCs+rq-yWz1Lw@mail.gmail.com>
References: 
 <CAPL5KHp2AfXb_xi+-gpffaS1-idNarvxFuUOqkCs+rq-yWz1Lw@mail.gmail.com>
Comments: In-reply-to =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?=
 <firstdismay@gmail.com>
	message dated "Fri, 02 Jul 2021 17:04:47 +0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <807950.1625234650.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 02 Jul 2021 10:04:10 -0400
Message-ID: <807951.1625234650@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/807951.1625234650%40sss.pgh.pa.us>
Precedence: bulk

=3D?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?=3D <firstdismay@gmail.co=
m> writes:
> I cannot understand the behavior of the last_value function. Conditions
> opposite to first_value conditions. Why such a result?

The default window frame isn't symmetrical is why.  Per the manual [1]:

    Note that first_value, last_value, and nth_value consider only the
    rows within the =E2=80=9Cwindow frame=E2=80=9D, which by default conta=
ins the rows
    from the start of the partition through the last peer of the current
    row. This is likely to give unhelpful results for last_value and
    sometimes also nth_value. You can redefine the frame by adding a
    suitable frame specification (RANGE, ROWS or GROUPS) to the OVER
    clause. See Section 4.2.8 for more information about frame
    specifications.

That is, if you do nothing to change it then first_value returns the
value at the first row of the partition, while last_value returns the
value at the last peer of the current row (which isn't even very
well-defined when the current row has some peers).

			regards, tom lane

[1] https://www.postgresql.org/docs/current/functions-window.html