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.