public inbox for [email protected]
help / color / mirror / Atom feedFrom: Marcos Pegoraro <[email protected]>
To: Shaozhong SHI <[email protected]>
Cc: pgsql-sql <[email protected]>
Subject: Re: Change detection
Date: Fri, 9 Dec 2022 14:00:08 -0300
Message-ID: <CAB-JLwa2voYyJUmzfanAJzvi0oW4SqEeQd5K2iO8bA_N8PE+nA@mail.gmail.com> (raw)
In-Reply-To: <CA+i5Jwbi6GVDT5d_HUTpQ+RMupjCm4hR4RkojLVgZPdv96LsmQ@mail.gmail.com>
References: <CA+i5JwZ+XmOF6P4Lo=UTVUJBSQN4EyQL-fe7t4XTLPB9bwq9aQ@mail.gmail.com>
<CAB-JLwZCEZNs_2jpd1UxRhC1=L3X0VeubokhQ5QBvhz3RckcZA@mail.gmail.com>
<CA+i5Jwbi6GVDT5d_HUTpQ+RMupjCm4hR4RkojLVgZPdv96LsmQ@mail.gmail.com>
just change lag(department) over(order by year) to lag(department)
over(partition by name order by year)
Atenciosamente,
Em sex., 9 de dez. de 2022 às 11:15, Shaozhong SHI <[email protected]>
escreveu:
> How about finding all changes for all people in a large record set?
>
> See the follwoing:
>
> David
>
> 1 Tom Sales 1990
> 2 Tom Sales 1991
> 3 Tom Sales 1991
> 4 Tom Management 1992
> 5 Tom Management 1992
> 6 Tim finance 1982
> 7 Tim finance 1983
> 8 Tim management 1984
> 9 Tim management 1985
>
> On Fri, 9 Dec 2022 at 13:06, Marcos Pegoraro <[email protected]> wrote:
>
>> Data
>>>
>>> Staff_ID Name Department Year
>>> 1 Tom Sales 1990
>>> 2 Tom Sales 1991
>>> 3 Tom Sales 1991
>>> 4 Tom Management 1992
>>> 4 Tom Management 1992
>>>
>>> select *, coalesce(lag(department) over(order by year), department) <>
>> department Changed from (Values (1, 'Tom', 'Sales', 1990),(2, 'Tom',
>> 'Sales', 1991),(3, 'Tom', 'Sales', 1991),(4, 'Tom', 'Management', 1992),(4,
>> 'Tom', 'Management', 1992)) as x(Staff_ID, Name, Department, Year);
>> staff_id | name | department | year | changed
>> ----------+------+------------+------+---------
>> 1 | Tom | Sales | 1990 | f
>> 2 | Tom | Sales | 1991 | f
>> 3 | Tom | Sales | 1991 | f
>> 4 | Tom | Management | 1992 | t
>> 4 | Tom | Management | 1992 | f
>> (5 rows)
>>
>>
>
view thread (8+ 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: Change detection
In-Reply-To: <CAB-JLwa2voYyJUmzfanAJzvi0oW4SqEeQd5K2iO8bA_N8PE+nA@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