Re: Tom changed his job role many times

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

On Tue, 13 Dec 2022 at 01:02, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Tue, 13 Dec 2022 at 11:43, Shaozhong SHI <shishaozhong(at)gmail(dot)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
>