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 1p3e9p-0006JF-KI for pgsql-sql@arkaria.postgresql.org; Fri, 09 Dec 2022 14:15:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p3e9o-0000lG-BQ for pgsql-sql@arkaria.postgresql.org; Fri, 09 Dec 2022 14:15:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p3e9o-0000kx-1E for pgsql-sql@lists.postgresql.org; Fri, 09 Dec 2022 14:15:20 +0000 Received: from mail-ej1-x630.google.com ([2a00:1450:4864:20::630]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p3e9m-00047l-1S for pgsql-sql@lists.postgresql.org; Fri, 09 Dec 2022 14:15:19 +0000 Received: by mail-ej1-x630.google.com with SMTP id x22so11778772ejs.11 for ; Fri, 09 Dec 2022 06:15:17 -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=hqBxyMNy9ac4im80jbZruZSHG+Tmsi4hGSiiaKP2mU0=; b=flHCB/bfCEBAzZNWrpluGwdBVWRBeXtAJ+oCapEKptLSU4Rn6nmHbgaIlrpNbJRa/d LZRovvOXC9XeMmodmIYNGB6Tt/S0ha5ZF05nE6mDQq9sXt/oSu4RXihv2Fg49A/FFkhI lgnscmgtFC7AgJCiQvZNVoudoQ0lcuuCLXoZo+hWA2W9iXSnoOXA2mP1lkVXJKERNX48 XYzmsmHPJJe7ePfIFj6yu4pQxbGFOUTU+q2WtGvVVwVEJrFRZtnoyohGMp0/dK20FIIZ T4mQy6iLUu4gU6rGta9wseeuFLOk3/RXiuKHg/yxTkGHioB3BRTQnaVaKeSJlP1tBVUT 0E/w== 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=hqBxyMNy9ac4im80jbZruZSHG+Tmsi4hGSiiaKP2mU0=; b=j68uCZWvy9BSScaXiWM8h9SpzvU8lo/2kqgugQI6qNt9+XVdNdVci4myVYXvcpdRHT x9w1NJd4jnecVsS4xokM5iFqI5/lCBLuEho195z03NlnOFy1V8PPVjRvfdHq7vY1s1xV ayVAYPDWESXZYOhmLEwAWs6bHdo5kG84DlyMYguiVsPAquG+BelQ9p15FNeQYCuABXMv Mi3Pm5lvB7gGWRYy5f+lOYhScGKrJ8fv1bj6Nv1VFzxv/odwwNyEDxGX7LlVN6Xxh9ud bTZp6nwX4muCiWkrBih6P4X6LTfYFQyISKuKig0x9rwRPN7HEyifbI5tqzdlCPrJu6V2 jUGA== X-Gm-Message-State: ANoB5plTZ8m4rVvRaiVvRuUtG2fbPanCi9Cn5hSGsNRLGZZdCnokFNwm qA9BACbx7p6a16vBuUvr7SXRfEmyJXfbkhG8ChY= X-Google-Smtp-Source: AA0mqf6uhIPt+9ThE+xHfYUbf8ic4ZeS+fvmmbjBGeoXTkBiTGO4QBs/2yL989TGjdh5sJDtFzkqtVy4umYXa1/7pNs= X-Received: by 2002:a17:906:38cd:b0:7be:4d3c:1a44 with SMTP id r13-20020a17090638cd00b007be4d3c1a44mr45578892ejd.543.1670595317376; Fri, 09 Dec 2022 06:15:17 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Shaozhong SHI Date: Fri, 9 Dec 2022 14:15:06 +0000 Message-ID: Subject: Re: Change detection To: Marcos Pegoraro Cc: pgsql-sql Content-Type: multipart/alternative; boundary="00000000000074ac6605ef65c853" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000074ac6605ef65c853 Content-Type: text/plain; charset="UTF-8" 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 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) > > --00000000000074ac6605ef65c853 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: base64 PGRpdiBkaXI9Imx0ciI+PGRpdiBkaXI9Imx0ciI+PGRpdiBkaXI9Imx0ciI+PGRpdiBkaXI9Imx0 ciI+PGRpdiBzdHlsZT0iY29sb3I6cmdiKDgwLDAsODApIj5Ib3cgYWJvdXQgZmluZGluZyBhbGwg Y2hhbmdlcyBmb3IgYWxsIHBlb3BsZSBpbiBhIGxhcmdlIHJlY29yZCBzZXQ/PC9kaXY+PGRpdiBz dHlsZT0iY29sb3I6cmdiKDgwLDAsODApIj48YnI+PC9kaXY+PGRpdiBzdHlsZT0iY29sb3I6cmdi KDgwLDAsODApIj5TZWUgdGhlIGZvbGx3b2luZzo8L2Rpdj48ZGl2IHN0eWxlPSJjb2xvcjpyZ2Io ODAsMCw4MCkiPjxicj48L2Rpdj48ZGl2IHN0eWxlPSJjb2xvcjpyZ2IoODAsMCw4MCkiPkRhdmlk PC9kaXY+PGRpdiBzdHlsZT0iY29sb3I6cmdiKDgwLDAsODApIj48YnI+PC9kaXY+PGRpdiBzdHls ZT0iY29sb3I6cmdiKDgwLDAsODApIj4xwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgVG9twqAgwqAg U2FsZXPCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoDE5OTA8L2Rpdj48ZGl2IHN0eWxl PSJjb2xvcjpyZ2IoODAsMCw4MCkiPjLCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoFRvbcKgIMKg IMKgIFNhbGVzwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAxOTkxPC9kaXY+PGRpdiBzdHls ZT0iY29sb3I6cmdiKDgwLDAsODApIj4zwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBUb23CoCDC oCDCoCBTYWxlc8KgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgMTk5MTxicj48L2Rpdj48ZGl2 IHN0eWxlPSJjb2xvcjpyZ2IoODAsMCw4MCkiPjTCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoFRv bcKgIMKgIMKgIE1hbmFnZW1lbnTCoCDCoCDCoCDCoCDCoDE5OTI8YnI+PC9kaXY+PGRpdiBzdHls ZT0iY29sb3I6cmdiKDgwLDAsODApIj41wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBUb23CoCDCoCDC oE1hbmFnZW1lbnTCoMKgIMKgIMKgIMKgIMKgIDE5OTI8L2Rpdj48ZGl2IHN0eWxlPSJjb2xvcjpy Z2IoODAsMCw4MCkiPjbCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCBUaW3CoCDCoCDCoGZpbmFuY2XC oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoDE5ODI8L2Rpdj48ZGl2IHN0eWxlPSJjb2xvcjpy Z2IoODAsMCw4MCkiPjfCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCBUaW3CoCDCoCDCoGZpbmFuY2XC oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoDE5ODM8YnI+PC9kaXY+PGRpdiBzdHlsZT0iY29s b3I6cmdiKDgwLDAsODApIj48ZGl2PjjCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoFRpbcKgIMKgIMKg bWFuYWdlbWVudMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgMTk4NDwvZGl2PjxkaXY+OcKg IMKgIMKgIMKgIMKgIMKgIMKgIMKgIFRpbcKgIMKgIG1hbmFnZW1lbnTCoCDCoCDCoCDCoCDCoCDC oCDCoCDCoCDCoCAxOTg1PC9kaXY+PC9kaXY+PC9kaXY+PC9kaXY+PC9kaXY+PC9kaXY+PGJyPjxk aXYgY2xhc3M9ImdtYWlsX3F1b3RlIj48ZGl2IGRpcj0ibHRyIiBjbGFzcz0iZ21haWxfYXR0ciI+ T24gRnJpLCA5IERlYyAyMDIyIGF0IDEzOjA2LCBNYXJjb3MgUGVnb3Jhcm8gJmx0OzxhIGhyZWY9 Im1haWx0bzptYXJjb3NAZjEwLmNvbS5iciI+bWFyY29zQGYxMC5jb20uYnI8L2E+Jmd0OyB3cm90 ZTo8YnI+PC9kaXY+PGJsb2NrcXVvdGUgY2xhc3M9ImdtYWlsX3F1b3RlIiBzdHlsZT0ibWFyZ2lu OjBweCAwcHggMHB4IDAuOGV4O2JvcmRlci1sZWZ0OjFweCBzb2xpZCByZ2IoMjA0LDIwNCwyMDQp O3BhZGRpbmctbGVmdDoxZXgiPjxkaXYgZGlyPSJsdHIiPjxkaXYgY2xhc3M9ImdtYWlsX3F1b3Rl Ij48YmxvY2txdW90ZSBjbGFzcz0iZ21haWxfcXVvdGUiIHN0eWxlPSJtYXJnaW46MHB4IDBweCAw cHggMC44ZXg7Ym9yZGVyLWxlZnQ6MXB4IHNvbGlkIHJnYigyMDQsMjA0LDIwNCk7cGFkZGluZy1s ZWZ0OjFleCI+PGRpdiBkaXI9Imx0ciI+PGRpdiBkaXI9Imx0ciI+PGRpdiBkaXI9Imx0ciI+PGRp diBkaXI9Imx0ciI+PGRpdiBkaXI9Imx0ciI+PGRpdj5EYXRhPC9kaXY+PGRpdj48YnI+PC9kaXY+ PGRpdj5TdGFmZl9JRMKgIMKgIE5hbWXCoCDCoERlcGFydG1lbnTCoCDCoCDCoCDCoCDCoCDCoCBZ ZWFyPC9kaXY+PGRpdj4xwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgVG9twqAgwqAgU2FsZXPCoCDC oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoDE5OTA8L2Rpdj48ZGl2PjLCoCDCoCDCoCDCoCDC oCDCoCDCoCDCoCDCoFRvbcKgIMKgIMKgIFNhbGVzwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAg wqAxOTkxPC9kaXY+PGRpdj4zwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBUb23CoCDCoCDCoCBT YWxlc8KgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgMTk5MTxicj48L2Rpdj48ZGl2PjTCoCDC oCDCoCDCoCDCoCDCoCDCoCDCoCDCoFRvbcKgIMKgIMKgIE1hbmFnZW1lbnTCoCDCoCDCoCDCoCDC oDE5OTI8YnI+PC9kaXY+PGRpdj40wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBUb23CoCDCoCDC oE1hbmFnZW1lbnTCoMKgIMKgIMKgIMKgIMKgIDE5OTI8YnI+PC9kaXY+PGRpdj48YnI+PC9kaXY+ PC9kaXY+PC9kaXY+PC9kaXY+PC9kaXY+PC9kaXY+PC9ibG9ja3F1b3RlPjxkaXY+PGZvbnQgZmFj ZT0idmVyZGFuYSwgc2Fucy1zZXJpZiIgY29sb3I9IiM2NjY2NjYiPjxzcGFuIGNsYXNzPSJnbWFp bF9kZWZhdWx0Ij48L3NwYW4+c2VsZWN0ICosIGNvYWxlc2NlKGxhZyhkZXBhcnRtZW50KSBvdmVy KDxzcGFuIGNsYXNzPSJnbWFpbF9kZWZhdWx0Ij5vcmRlciBieSB5ZWFyPC9zcGFuPiksIGRlcGFy dG1lbnQpICZsdDsmZ3Q7IGRlcGFydG1lbnQgQ2hhbmdlZCBmcm9tIChWYWx1ZXMgKDEsICYjMzk7 VG9tJiMzOTssICYjMzk7U2FsZXMmIzM5OywgMTk5MCksKDIsICYjMzk7VG9tJiMzOTssICYjMzk7 U2FsZXMmIzM5OywgMTk5MSksKDMsICYjMzk7VG9tJiMzOTssICYjMzk7U2FsZXMmIzM5OywgMTk5 MSksKDQsICYjMzk7VG9tJiMzOTssICYjMzk7TWFuYWdlbWVudCYjMzk7LCAxOTkyKSwoNCwgJiMz OTtUb20mIzM5OywgJiMzOTtNYW5hZ2VtZW50JiMzOTssIDE5OTIpKSBhcyB4KFN0YWZmX0lELCBO YW1lLCBEZXBhcnRtZW50LCBZZWFyKTs8L2ZvbnQ+PC9kaXY+PGZvbnQgZmFjZT0idmVyZGFuYSwg c2Fucy1zZXJpZiIgY29sb3I9IiM2NjY2NjYiPsKgc3RhZmZfaWQgfCBuYW1lIHwgZGVwYXJ0bWVu dCB8IHllYXIgfCBjaGFuZ2VkIDxicj4tLS0tLS0tLS0tKy0tLS0tLSstLS0tLS0tLS0tLS0rLS0t LS0tKy0tLS0tLS0tLTxicj7CoCDCoCDCoCDCoCAxIHwgVG9tIMKgfCBTYWxlcyDCoCDCoCDCoHwg MTk5MCB8IGY8YnI+wqAgwqAgwqAgwqAgMiB8IFRvbSDCoHwgU2FsZXMgwqAgwqAgwqB8IDE5OTEg fCBmPGJyPsKgIMKgIMKgIMKgIDMgfCBUb20gwqB8IFNhbGVzIMKgIMKgIMKgfCAxOTkxIHwgZjxi cj7CoCDCoCDCoCDCoCA0IHwgVG9tIMKgfCBNYW5hZ2VtZW50IHwgMTk5MiB8IHQ8YnI+wqAgwqAg wqAgwqAgNCB8IFRvbSDCoHwgTWFuYWdlbWVudCB8IDE5OTIgfCBmPGJyPig1IHJvd3MpPC9mb250 Pjxicj48ZGl2PjxzcGFuIGNsYXNzPSJnbWFpbF9kZWZhdWx0IiBzdHlsZT0iZm9udC1mYW1pbHk6 dmVyZGFuYSxzYW5zLXNlcmlmO2NvbG9yOnJnYigxMDIsMTAyLDEwMikiPjwvc3Bhbj7CoDwvZGl2 PjwvZGl2PjwvZGl2Pg0KPC9ibG9ja3F1b3RlPjwvZGl2Pg0K --00000000000074ac6605ef65c853--