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 1gRMQh-0004zT-Pb for pgsql-sql@arkaria.postgresql.org; Mon, 26 Nov 2018 19:20:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gRMQg-0007JJ-HN for pgsql-sql@arkaria.postgresql.org; Mon, 26 Nov 2018 19:20:22 +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 1gRMQg-0007JC-5c for pgsql-sql@lists.postgresql.org; Mon, 26 Nov 2018 19:20:22 +0000 Received: from mail-pl1-x636.google.com ([2607:f8b0:4864:20::636]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gRMQd-0005re-47 for pgsql-sql@lists.postgresql.org; Mon, 26 Nov 2018 19:20:20 +0000 Received: by mail-pl1-x636.google.com with SMTP id t13so14324981ply.13 for ; Mon, 26 Nov 2018 11:20:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=3ng0FTC5lMesyJC9snoKBHoi58kG9Lao7afiRUCtC8g=; b=fSfXAy6w0Q+FzZp5rFdeHTDEn2cFwI/bUiPdo9cq7fiHqLSUwVr0b1GI/HMjIRk6u4 WFn7w90BbnyES3kzDEEK4pKc805dXLV0HtTO0T8yk0SPQBxS/HUp5FxEa9Bqdvq5sW5/ tMzWP+HtZnQQo1eVIIdRsgM6ylzzJH2K3MNb4OaiaKx5Y4mWkoRUQxg8lj8YyhJSE5T6 U9IZ5lPrQN582H6neXMQRYtlGo0odqpe3D9lPUxsg9U3VpGBIvNGKzYpUQvs2nchXJj4 IkTVn6WVROKReUtn0SaY5PbjECf1UMGgQJ2UOIIcvNqSLCTu7OH7A/uj5tnr1uniVvgC GFuw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=3ng0FTC5lMesyJC9snoKBHoi58kG9Lao7afiRUCtC8g=; b=XYA5a4WoQltl8B2IF+8rEMN96SX1O8uUhdhQmY8pu+yz9HicAT47ONZ/SIHoZIx3hA yuCtvaS/bC38Lf9TcIVEaOex0TCyeAsPP3vHV22mTtUY/YqDuR/0CzUBC5OuKDu5Ekxt N50xs7yKWuwq2Zq0tDM2vQdphRNSrUCl/m0c7cy3jmfFgfQWKPbvblAwSjCbW08Btx2a 5P/XRJG0EBWnfgF/A1H3MkYFd8a9F0GIA50nd9ySq3S08NplvfEClVxHyBQZYYtsFUvA +t8tJl1K/yCFZVSBV2KcbACWWAfu61jrzayGmJKb+xe+Ztg8uUmxw9bRnhlIKjzhCvaO wnSQ== X-Gm-Message-State: AA+aEWbkymi5dSVGoEkxE6PIypc8hzhg1rMMfMOF/8pI/gdIN20YoweO gt0f8rLvXLHFvDbNbUKn3aM= X-Google-Smtp-Source: AFSGD/Wb7OaiOMyR+djIvxrBuOaoAdvYRHB9wlFZjdqquuM41qzqnKZyNCLfwKmrlBtpcAyMnP5myQ== X-Received: by 2002:a17:902:380c:: with SMTP id l12mr28447344plc.326.1543260017790; Mon, 26 Nov 2018 11:20:17 -0800 (PST) Received: from kitselas.hci.utah.edu ([155.100.47.5]) by smtp.gmail.com with ESMTPSA id v5sm2605011pgn.5.2018.11.26.11.20.14 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 26 Nov 2018 11:20:15 -0800 (PST) From: Rob Sargent Message-Id: <57AC3D96-24B8-4D06-8946-AF6E0116888F@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_96387E69-FB4D-40A5-848F-7D111FDB12AF" Mime-Version: 1.0 (Mac OS X Mail 12.1 \(3445.101.1\)) Subject: Re: Select Distinct Order By Array_Position Date: Mon, 26 Nov 2018 12:20:14 -0700 In-Reply-To: <004301d485bc$00347250$009d56f0$@gmail.com> Cc: pgsql-sql@lists.postgresql.org To: Mark Williams References: <004301d485bc$00347250$009d56f0$@gmail.com> X-Mailer: Apple Mail (2.3445.101.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_96387E69-FB4D-40A5-848F-7D111FDB12AF Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > 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 > __ Try put the array_position clause in the select and add documents.id = to the order by? --Apple-Mail=_96387E69-FB4D-40A5-848F-7D111FDB12AF Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

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?
= --Apple-Mail=_96387E69-FB4D-40A5-848F-7D111FDB12AF--