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 1qIAeU-0002II-Hg for pgsql-sql@arkaria.postgresql.org; Sat, 08 Jul 2023 16:19:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qIAeS-0006PB-UU for pgsql-sql@arkaria.postgresql.org; Sat, 08 Jul 2023 16:19:16 +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 1qIAeS-0006P2-Kc for pgsql-sql@lists.postgresql.org; Sat, 08 Jul 2023 16:19:16 +0000 Received: from mout.gmx.net ([212.227.17.20]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qIAeP-002jcz-Ry for pgsql-sql@lists.postgresql.org; Sat, 08 Jul 2023 16:19:15 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1688833151; x=1689437951; i=shammat@gmx.net; bh=eWPLmU+soSGONktUoGK625erc6zFkE6UUnvhrZyMg1I=; h=X-UI-Sender-Class:Subject:To:References:From:Date:In-Reply-To; b=CYF9Qlmsm1a6kb+E8MlAcYMieWXeUD/zCegs7yc5OKbt+109o8SjaSBTQRFw9GjM2qomxr6 OSL/A8wsmWLyGngIe3nNXwzrTxZfd/h+WqNIwKCIJav9ic3U7SAyVE/iWHWKVwPy02zy3EDW3 Q2yVWAJfB9NRRXR1ufbEJuImVX++EopGkvCdYwWVoDdwAjdc2NZ+6ygdeVltDV+cu2dJ53iOb +wjZSPrbQiVEtE1L0X+1ZPLc5GL/epedPFaxPGheHje0Vq91g5V49yQcsafOsrzZzq1qWEGa8 qutAhdNAmMfCJJGsWg867EkkxKoSm914a4vq049t6CXz8MZAUV/A== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [192.168.178.20] ([88.217.180.50]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MbivG-1piH8D1BUc-00dE70 for ; Sat, 08 Jul 2023 18:19:11 +0200 Subject: Re: How to remove duplicates in an array and maintain the order? To: pgsql-sql@lists.postgresql.org References: From: Thomas Kellerer Message-ID: <320adc31-e193-14b9-e9b9-80625099b401@gmx.net> Date: Sat, 8 Jul 2023 18:19:10 +0200 User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: de-DE Content-Transfer-Encoding: 7bit X-Provags-ID: V03:K1:m6w3pr+wN4UPPqEoGxzuBHAVFnh8r/OXIV+dVueblkHcJLQi8Dw IEhDuakG05muYx3w+4RW49e0CZh+KjUvs8Cn06xe36zIwklkj0KgIo4GCx8/+g0sGjCFEL7 skAkgMDPRqBXrmpUCgNZ6p/txe4Kt1i9xCErvgha/MrAf+zt9vDjkJ/k8JYejEHD6JK/VSa AOUAqOSvsLMe7c8YDMRQw== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:BpEdSndTYpo=;QQ0O7W3FQQs8njVZLgTL9Md7Yic WpVWFGOPP1f4zsztwH6+P0ZNljfShvOYqKKxm9b+WXb1EZOtxO0sTByndl6GfDfUsocNfujH4 wdKwYS8yXqfw7ZnC2OEHzQjbQiMK0oy5BvXy+AeM64PbTqndCNu2op3KghGanV/ZGhdBCrND4 zN0Xyhf4PDgmR0F8h6YUdGqaVArFe6bU1v8Ey6HQTyUkCpUR2dvfoIX94hDGuIpf7Z2Y+YIju WrzEDSYli6b4HCSOyskMhdLqfOqRi/qXJEN/zGLrSwAWu1htIW7lWVn9nJHLteAin0PFRCGIj v18pY5cR5+kGhnkxsMlECqv8zCqEouaw2lKqB2e6R6veQAtLrO/CALjmkjcr/4rADe6O1nnc0 yhGFOmR/kxn+CULhDmd3AN2Ud9HKAn7TG7Gxez0RHK4R28zTiCAGD4xMRmHVYuXyq803KnAix Sg3xGfyEZKReupX4Ee4r2WZFoq123Y0UVp8n0bFlGxDRfhvT3MBjj6Ng28SOx80ciKaCvu7hl 7oRHwFdgs9k8sB1rOcjsRUaMOTHW+hQPBF+kEYetnileRbTncWtwNWtVyzCW7qW1xPcyu7ac1 1R51UXMDDb0uk1LBjaqFPHtajqXrBzBEKTdDoXNY6hJl/7RCemIaxutkOBx4zJRqbJkXEVhLa tp4UcNMiaUY1SSHs5ZwsXWhpKUCMGZR6YfiPibWBNsHbpvMVm7JGyVGlbZ4GRs/NN0kl5NlZV eRavcoa77nveT1oDfmt7fxLX0bXwvowxpEVcK9wxOXHUvB89qAfs6mMB+YB95FqUmM3fvn8h1 vWaN1Kn9hiZ9zhBYnKRFqea8gap+rBMFg1Fo1BRvujf8cz5bufP7HwoP3Gm1y+hPi9RhcnFJP 1su6FWlMaChxRl1Wvpt3qQeB8C4NdpodBCF2vzq8HK8P37WIIJB4+wVw9ISNgjzDNNziCR5wA 9yZTknILQq/HWBYdWzmnG/JodIg= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Shaozhong SHI schrieb am 08.07.2023 um 17:30: > How to remove duplicated values in an array and maintain the order? You can use distinct on () select array_agg(val order by idx) from ( select distinct on (val) val, idx from unnest(array[7,7,5,6,2,2,3,8,7,5,4,1,5]) with ordinality as t(val,idx) order by val, idx ) x This picks the first occurrance of each element. If you want to get the last occurrance of each value use "order by val, idx desc" in the inner select