public inbox for [email protected]  

help / color / mirror / Atom feed

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

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

On Tue, 13 Dec 2022 at 01:02, David Rowley <[email protected]> wrote:

> 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)

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: <CA+i5JwbS4uDD252hcGjX7zsHzLNeiT9BveSmvK9BvgTNgKz+zA@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