public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: Shaozhong SHI <[email protected]>
Cc: pgsql-sql <[email protected]>
Subject: Re: Tom changed his job role many times
Date: Tue, 13 Dec 2022 14:01:35 +1300
Message-ID: <CAApHDvonJhn3KiEPhKhMPz37qn+y+iyB2JRptguqs4D2dJtg8A@mail.gmail.com> (raw)
In-Reply-To: <CA+i5JwbWP5CpuQ5v=orYUUW-r0mLdrNpgGy4fUeC4UhWkAYYZQ@mail.gmail.com>
References: <CA+i5JwbWP5CpuQ5v=orYUUW-r0mLdrNpgGy4fUeC4UhWkAYYZQ@mail.gmail.com>

On Tue, 13 Dec 2022 at 11:43, Shaozhong SHI <[email protected]> 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

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Tom changed his job role many times
  In-Reply-To: <CAApHDvonJhn3KiEPhKhMPz37qn+y+iyB2JRptguqs4D2dJtg8A@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox