MIME-Version: 1.0
References: 
 <CA+i5JwbWP5CpuQ5v=orYUUW-r0mLdrNpgGy4fUeC4UhWkAYYZQ@mail.gmail.com>
 <CAApHDvonJhn3KiEPhKhMPz37qn+y+iyB2JRptguqs4D2dJtg8A@mail.gmail.com>
In-Reply-To: 
 <CAApHDvonJhn3KiEPhKhMPz37qn+y+iyB2JRptguqs4D2dJtg8A@mail.gmail.com>
From: Shaozhong SHI <shishaozhong@gmail.com>
Date: Tue, 13 Dec 2022 20:33:37 +0000
Message-ID: 
 <CA+i5JwbS4uDD252hcGjX7zsHzLNeiT9BveSmvK9BvgTNgKz+zA@mail.gmail.com>
Subject: Re: Tom changed his job role many times
To: David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000810b4205efbb89ee"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2Bi5JwbS4uDD252hcGjX7zsHzLNeiT9BveSmvK9BvgTNgKz%2BzA%40mail.gmail.com>
Precedence: bulk

--000000000000810b4205efbb89ee
Content-Type: text/plain; charset="UTF-8"

Thanks, David.  So many different ways to produce answers.  Brilliant.
Regards,  David

On Tue, 13 Dec 2022 at 01:02, David Rowley <dgrowleyml@gmail.com> wrote:

> On Tue, 13 Dec 2022 at 11:43, Shaozhong SHI <shishaozhong@gmail.com>
> wrote:
> > We want to pick up only the records before and after his role change to
> show what happened.
>
> Window functions cannot appear in the WHERE clause, but you can add a
> subquery and perform the filtering there.
>
> SELECT * FROM (
>     SELECT name,year,department,
>            lag(department) over (partition by name order by year)
> last_department
>     from mytable) t
> WHERE department <> last_department or last_department is null;
>
> what you put in the WHERE clause will depend on what you want to
> happen when the employee is first hired. last_department will be NULL
> because I didn't add any default to lag like you did. You may want to
> check that the COALESCE() is what you want.  You may want to use
> LAG(department, 1, department) instead.  These are the same if
> department is defined as NOT NULL, but not if there is null values in
> there.
>
> There are a few details in [1] about window functions and how to
> filter on them that you might get something out of reading.
>
> David
>
> [1] https://www.postgresql.org/docs/current/tutorial-window.html
>

--000000000000810b4205efbb89ee
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Thanks, David.=C2=A0 So many different ways to produce ans=
wers.=C2=A0 Brilliant.=C2=A0 Regards,=C2=A0 David</div><br><div class=3D"gm=
ail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, 13 Dec 2022 at 01:=
02, David Rowley &lt;<a href=3D"mailto:dgrowleyml@gmail.com">dgrowleyml@gma=
il.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"m=
argin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left=
:1ex">On Tue, 13 Dec 2022 at 11:43, Shaozhong SHI &lt;<a href=3D"mailto:shi=
shaozhong@gmail.com" target=3D"_blank">shishaozhong@gmail.com</a>&gt; wrote=
:<br>
&gt; We want to pick up only the records before and after his role change t=
o show what happened.<br>
<br>
Window functions cannot appear in the WHERE clause, but you can add a<br>
subquery and perform the filtering there.<br>
<br>
SELECT * FROM (<br>
=C2=A0 =C2=A0 SELECT name,year,department,<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0lag(department) over (partition by=
 name order by year)<br>
last_department<br>
=C2=A0 =C2=A0 from mytable) t<br>
WHERE department &lt;&gt; last_department or last_department is null;<br>
<br>
what you put in the WHERE clause will depend on what you want to<br>
happen when the employee is first hired. last_department will be NULL<br>
because I didn&#39;t add any default to lag like you did. You may want to<b=
r>
check that the COALESCE() is what you want.=C2=A0 You may want to use<br>
LAG(department, 1, department) instead.=C2=A0 These are the same if<br>
department is defined as NOT NULL, but not if there is null values in<br>
there.<br>
<br>
There are a few details in [1] about window functions and how to<br>
filter on them that you might get something out of reading.<br>
<br>
David<br>
<br>
[1] <a href=3D"https://www.postgresql.org/docs/current/tutorial-window.html=
" rel=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/docs/curr=
ent/tutorial-window.html</a><br>
</blockquote></div>

--000000000000810b4205efbb89ee--