Re: Change detection - Mailing list pgsql-sql
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 <shishaozhong@gmail.com> escreveu:
How about finding all changes for all people in a large record set?See the follwoing:David1 Tom Sales 19902 Tom Sales 19913 Tom Sales 19914 Tom Management 19925 Tom Management 19926 Tim finance 19827 Tim finance 19838 Tim management 19849 Tim management 1985On Fri, 9 Dec 2022 at 13:06, Marcos Pegoraro <marcos@f10.com.br> wrote:DataStaff_ID Name Department Year1 Tom Sales 19902 Tom Sales 19913 Tom Sales 19914 Tom Management 19924 Tom Management 1992select *, 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)