Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p3dCz-0003I4-AM for pgsql-sql@arkaria.postgresql.org; Fri, 09 Dec 2022 13:14:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p3dCy-0007bD-0T for pgsql-sql@arkaria.postgresql.org; Fri, 09 Dec 2022 13:14:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p3dCx-0007b4-Mc for pgsql-sql@lists.postgresql.org; Fri, 09 Dec 2022 13:14:31 +0000 Received: from mail-ed1-x52b.google.com ([2a00:1450:4864:20::52b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p3dCv-0002Hc-3m for pgsql-sql@lists.postgresql.org; Fri, 09 Dec 2022 13:14:30 +0000 Received: by mail-ed1-x52b.google.com with SMTP id f7so3021505edc.6 for ; Fri, 09 Dec 2022 05:14:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=QsWISZ2Gt0YvJYEwp56vFZoXnJBPKvhAS2/8y3nF5Pw=; b=MAggRcBP1hCulOyl4tsdiSDaydhNXurFK693rdKYYTC7k2uzy0HdD3sSZrggm8j7C0 KavkPylstX4KysHQOqX55BoBOKkqrc/uefuzDcQivgHGb9D64NokqfzifHFNMLFg/y9i cd/9h/vLZ9EfHyau9QpB57uviq1GA/5ntXs8L7qLfgLYsj+jTuFO8R9rGwUMpwtQrVAp Jdaz3RsH58fJ4+eJpdCVQArXS8t0oG4uR8GPf/d3Ce2BKx0WdBrUC4c4T2hGyWRlpf1P 6KnLJGwUs5Lujq9CWY8cHXmQJeup383u8Txq8nFnC8Jk6yWHjTj844BEMtppsdapMoHv r/Yw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=QsWISZ2Gt0YvJYEwp56vFZoXnJBPKvhAS2/8y3nF5Pw=; b=F3miisj0QPMPxVcUPTdBfJDkRKnG4XTNOd4XEPo4zKefI0UNrOyOVkDUrvvw+EV70r xn2ThEWCRKTMvMtSvayXdkvNW7Aor1VqaBz+smlzIkuLzCWB49arKjH2jAO01Oo5DnrK HPC7yzPPjEFkZWYDaCChoVmxl/a9yOECUC8y6iAMzp46gL8tM3p3d9B0HRsgwa4RPjJg ljZD9afNd4xciK2XxfB1r3TPdW++0V+2BaGXIsgeAecaxCckX2JqNSH8oiratCXmKlsr 3zcxQSMajWcJvym4LEvetqKQv2eoPg3n4JTb3hhtAq0oLr+QAuicy89XVQQWuvsFlHmS ilNQ== X-Gm-Message-State: ANoB5plugNjQVY5acenhUiawf+pBdODXJFbvQxqp8KgccF/PmDt7irtw F7S9+oPc4zrYzLxemdEWg2mEcCHxAq4gFj0Szwo= X-Google-Smtp-Source: AA0mqf7gTWYGNmTe/5e24AMpk5TyLFkntoiPdI6WAy5Wl+ibN9w0Z4eIZpPtcTRRQf6s6rcwhiQ2TrpPAlUFz/n/vt4= X-Received: by 2002:aa7:c719:0:b0:46a:bfd0:f816 with SMTP id i25-20020aa7c719000000b0046abfd0f816mr21664805edq.277.1670591667235; Fri, 09 Dec 2022 05:14:27 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Shaozhong SHI Date: Fri, 9 Dec 2022 13:14:15 +0000 Message-ID: Subject: Re: Change detection To: Marcos Pegoraro Cc: pgsql-sql Content-Type: multipart/alternative; boundary="000000000000e3f1e005ef64eebc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e3f1e005ef64eebc Content-Type: text/plain; charset="UTF-8" Thanks, Marcos. It worked well. Regards, David On Fri, 9 Dec 2022 at 13:06, Marcos Pegoraro 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) > > --000000000000e3f1e005ef64eebc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks, Marcos.=C2=A0 It worked well.=C2=A0 Regards, David=

= On Fri, 9 Dec 2022 at 13:06, Marcos Pegoraro <marcos@f10.com.br> wrote:
Data

Staff_ID=C2=A0 =C2=A0 Name=C2=A0 =C2=A0Department=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Year
1=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Tom=C2=A0 =C2=A0 Sales=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01990
2=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Tom=C2=A0 =C2=A0= =C2=A0 Sales=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A01991
3=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Tom=C2=A0 =C2=A0 =C2=A0 Sales=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01991
4=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Tom=C2=A0 =C2=A0 =C2=A0 Management= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A01992
4=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Tom=C2=A0 =C2=A0 =C2=A0Management= =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1992

select *, coalesc= e(lag(department) over(order by year),= department) <> department Changed from (Values (1, 'Tom', &#= 39;Sales', 1990),(2, 'Tom', 'Sales', 1991),(3, 'Tom= ', 'Sales', 1991),(4, 'Tom', 'Management', 1992= ),(4, 'Tom', 'Management', 1992)) as x(Staff_ID, Name, Depa= rtment, Year);
=C2=A0staff_id | name | department | year | changed
----------+---= ---+------------+------+---------
=C2=A0 =C2=A0 =C2=A0 =C2=A0 1 | Tom = =C2=A0| Sales =C2=A0 =C2=A0 =C2=A0| 1990 | f
=C2=A0 =C2=A0 =C2=A0 =C2=A0= 2 | Tom =C2=A0| Sales =C2=A0 =C2=A0 =C2=A0| 1991 | f
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 3 | Tom =C2=A0| Sales =C2=A0 =C2=A0 =C2=A0| 1991 | f
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 4 | Tom =C2=A0| Management | 1992 | t
=C2=A0 =C2=A0= =C2=A0 =C2=A0 4 | Tom =C2=A0| Management | 1992 | f
(5 rows)
=
=C2=A0
--000000000000e3f1e005ef64eebc--