MIME-Version: 1.0
References: 
 <CA+i5JwbWP5CpuQ5v=orYUUW-r0mLdrNpgGy4fUeC4UhWkAYYZQ@mail.gmail.com>
In-Reply-To: 
 <CA+i5JwbWP5CpuQ5v=orYUUW-r0mLdrNpgGy4fUeC4UhWkAYYZQ@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Tue, 13 Dec 2022 14:01:35 +1300
Message-ID: 
 <CAApHDvonJhn3KiEPhKhMPz37qn+y+iyB2JRptguqs4D2dJtg8A@mail.gmail.com>
Subject: Re: Tom changed his job role many times
To: Shaozhong SHI <shishaozhong@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvonJhn3KiEPhKhMPz37qn%2By%2BiyB2JRptguqs4D2dJtg8A%40mail.gmail.com>
Precedence: bulk

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