From: "Mark Williams" <markwillimas@gmail.com>
To: <pgsql-sql@lists.postgresql.org>
Subject: Select Distinct Order By Array_Position
Date: Mon, 26 Nov 2018 19:12:39 -0000
Message-ID: <004301d485bc$00347250$009d56f0$@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0044_01D485BC.00355CB0"
Thread-Index: AdSFu3uuQJTD+OWASuacYBzfGr4zXg==
Content-Language: en-gb
Precedence: bulk

This is a multipart message in MIME format.

------=_NextPart_000_0044_01D485BC.00355CB0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi,

 
I am getting an error "SELECT DISTINCT, ORDER BY expressions must appear in
select list". 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=texts.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

__

 
------=_NextPart_000_0044_01D485BC.00355CB0
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40"><head><meta =
http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii"><meta name=3DGenerator content=3D"Microsoft Word 15 =
(filtered medium)"><style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;
	mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:#0563C1;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:#954F72;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;
	mso-fareast-language:EN-US;}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body lang=3DEN-GB =
link=3D"#0563C1" vlink=3D"#954F72"><div class=3DWordSection1><p =
class=3DMsoNormal>Hi,<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>I am getting =
an error &#8220;SELECT DISTINCT, ORDER BY expressions must appear in =
select list&#8221;. 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.<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>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&nbsp; =
(text LIKE '%RIVER%') ORDER BY array_position(ARRAY[26194, 2345, =
189]::INTEGER[], documents.id)<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>Thanks,<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>Mark<o:p></o:p></p><p class=3DMsoNormal><span =
style=3D'mso-fareast-language:EN-GB'>__<o:p></o:p></span></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p></div></body></html>
------=_NextPart_000_0044_01D485BC.00355CB0--