Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gRN1r-0007XK-TQ for pgsql-sql@arkaria.postgresql.org; Mon, 26 Nov 2018 19:58:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gRN1q-0001hT-4Y for pgsql-sql@arkaria.postgresql.org; Mon, 26 Nov 2018 19:58:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gRN1p-0001hI-O7 for pgsql-sql@lists.postgresql.org; Mon, 26 Nov 2018 19:58:45 +0000 Received: from mail-wm1-x332.google.com ([2a00:1450:4864:20::332]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gRN1m-0006nR-E9 for pgsql-sql@lists.postgresql.org; Mon, 26 Nov 2018 19:58:44 +0000 Received: by mail-wm1-x332.google.com with SMTP id q26so19777084wmf.5 for ; Mon, 26 Nov 2018 11:58:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:cc:references:in-reply-to:subject:date:message-id :mime-version:thread-index:content-language; bh=Rn5+aNQ59qzobdiB+4ccNukJe1HPUa4mXiKZRtXCXyM=; b=U305RYPZ9eBRWJU36wz/OcnHYVD6zObOQKANiDJQD2/y5SLq4htyPxJLDOZww8S3Ap 9GHfQhpbfeYINH6KmcMuKYQkXBn6RjwSPKjFlufzm5dLadyzfH1OoNd28QWqOismLgDV elTrWteoiqjYiJBsaQNnDq3nCl5mrMZgRXjr0naB8qM7eARNiqNiWfODQBjYtZwWANzh 0qd/8X8y5SzQ8Xk8QurhVsZUZ7RQTmA0gxfgwkyunOtt6UVZMuUYIvAk462rvB+GyR/q C9exMOaUzBe4zoI2+D87oEzIzUImt7nghaRrRL1YjZ83RpmKwbSSJZ2zCpqHDUYwSVX9 feNg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:cc:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=Rn5+aNQ59qzobdiB+4ccNukJe1HPUa4mXiKZRtXCXyM=; b=YU4gQUWEySKEQ9HrghX2z8A78EDOuBg9Y5g2a6URLeepmFHR5Q4ZSvvI0g0I1QVnyv bLHsCiP3Nd/vRohogOq4opuUGlV/hw9P2PqgPNcYROZ8SErw/y2TbFNyiLIGHk21zvuU 9FNTCCQMj7AtdC7IjXnRWt5pvNb4mquC6bmKSfN/zHX6BqeHUYNVLFrXmW0rfuzl0aHK 0NBipxkTNRfL5kmbrhCx/rKS5CITTlyEATresFE2FRAxhMRa+YwLR17p3Br1fNGPLsqw E1SA7FmcjNDGqPdQYFXEmqUdyPytVRmFYfMkGED23n26PYzcZ9qP3GlBPBkzy/TnTDre ustg== X-Gm-Message-State: AGRZ1gI+08oXzOV2dsC9HGinmwhOYlWlJZlglBO4bdaAsR4mp9gsaToR kr5CA5L84oxZ67rcHq0z4SZk3VTe X-Google-Smtp-Source: AJdET5f93YpFkCcm/RxTNVQKoOdUDY1xr4TzrcPP/SF0AKmgYt1XiYYYTvS+gGxM30R/guaKHX4VUQ== X-Received: by 2002:a1c:9c0a:: with SMTP id f10mr25549537wme.73.1543262320382; Mon, 26 Nov 2018 11:58:40 -0800 (PST) Received: from LAPTOPE7EH7MDP (167.221-30-62.static.virginmediabusiness.co.uk. [62.30.221.167]) by smtp.gmail.com with ESMTPSA id y8sm1895276wmg.13.2018.11.26.11.58.39 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 26 Nov 2018 11:58:39 -0800 (PST) From: "Mark Williams" To: "'Rob Sargent'" Cc: References: <004301d485bc$00347250$009d56f0$@gmail.com> <57AC3D96-24B8-4D06-8946-AF6E0116888F@gmail.com> In-Reply-To: <57AC3D96-24B8-4D06-8946-AF6E0116888F@gmail.com> Subject: RE: Select Distinct Order By Array_Position Date: Mon, 26 Nov 2018 19:58:37 -0000 Message-ID: <004e01d485c2$6c45cb50$44d161f0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_004F_01D485C2.6C468EA0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQEpSAIENSM0BdolJjQVlTJ5xV69VAHbwdfGpqoWPwA= Content-Language: en-gb List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_004F_01D485C2.6C468EA0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Wasn=E2=80=99t aware it was possible to put array_position statement in = the actual select or is this a select within a select? =20 Also, I am selecting from an ordered (randomly) subset of data and I = need to return the result set in the same order so do have to output the = array as part of the order by? =20 __ =20 From: Rob Sargent =20 Sent: 26 November 2018 19:20 To: Mark Williams Cc: pgsql-sql@lists.postgresql.org Subject: Re: Select Distinct Order By Array_Position =20 =20 On Nov 26, 2018, at 12:12 PM, Mark Williams > wrote: =20 Hi, =20 I am getting an error =E2=80=9CSELECT DISTINCT, ORDER BY expressions = must appear in select list=E2=80=9D. I am ordering by = documents.id and it appears in my select list. So = I am guessing the problem lies with the array. Is there any way of = achieving this? Query is below. =20 SELECT DISTINCT documents.id, page_no FROM texts = LEFT JOIN documents on = documents.id=3Dtexts.doc_id WHERE doc_id IN (26194, 2345, 189) AND = (text LIKE '%RIVER%') ORDER BY array_position(ARRAY[26194, 2345, = 189]::INTEGER[], documents.id) =20 Thanks, =20 Mark __ =20 Try put the array_position clause in the select and add documents.id = to the order by? =20 ------=_NextPart_000_004F_01D485C2.6C468EA0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Wasn=E2=80=99t aware it was = possible to put array_position statement in the actual select or is this = a select within a select?

 

Also, I am = selecting from an ordered (randomly) subset of data and I need to return = the result set in the same order so do have to output the array as part = of the order by?

 

__

 

From: Rob Sargent = <robjsargent@gmail.com>
Sent: 26 November 2018 = 19:20
To: Mark Williams = <markwillimas@gmail.com>
Cc: = pgsql-sql@lists.postgresql.org
Subject: Re: Select Distinct = Order By Array_Position

 

 



On Nov 26, 2018, at 12:12 PM, Mark Williams <markwillimas@gmail.com> = wrote:

 

Hi,

 

I = am getting an error =E2=80=9CSELECT DISTINCT, ORDER BY expressions must = appear in select list=E2=80=9D. I am ordering by documents.id and it appears in my select = list. So I am guessing the problem lies with the array. Is there any way = of achieving this? Query is below.

 

SELECT DISTINCT documents.id, page_no FROM texts LEFT = JOIN documents on documents.id=3Dtexts.doc_id WHERE = doc_id IN (26194, 2345, 189) AND  (text LIKE '%RIVER%') ORDER BY = array_position(ARRAY[26194, 2345, 189]::INTEGER[], documents.id)

 

Thanks,

 

Mark

__

 

Try put = the array_position clause in the select and add documents.id to the order = by?

 

------=_NextPart_000_004F_01D485C2.6C468EA0--