From: "Mark Williams" <markwillimas@gmail.com>
To: "'David G. Johnston'" <david.g.johnston@gmail.com>
Cc: "'pgsql-sql'" <pgsql-sql@lists.postgresql.org>
References: <004301d485bc$00347250$009d56f0$@gmail.com>
 <CAKFQuwYroR1Q_+rF6odeDEA5+NLk6TjCYy-yTJUxf+ob7huhdg@mail.gmail.com>
In-Reply-To: 
 <CAKFQuwYroR1Q_+rF6odeDEA5+NLk6TjCYy-yTJUxf+ob7huhdg@mail.gmail.com>
Subject: RE: Select Distinct Order By Array_Position
Date: Mon, 26 Nov 2018 19:59:55 -0000
Message-ID: <005301d485c2$9a6f1db0$cf4d5910$@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AQEpSAIENSM0BdolJjQVlTJ5xV69VAG9YTmspqsKIcA=
Content-Language: en-gb
Precedence: bulk

David, thanks. It was a bad example on my part. Could just as well have =
been: (26194, 189, 2345).

__

-----Original Message-----
From: David G. Johnston <david.g.johnston@gmail.com>=20
Sent: 26 November 2018 19:46
To: Mark Williams <markwillimas@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Select Distinct Order By Array_Position

On Mon, Nov 26, 2018 at 12:12 PM Mark Williams <markwillimas@gmail.com> =
wrote:
> I am ordering by documents.id and it appears in my select list.
[...]
> SELECT DISTINCT documents.id, page_no FROM texts LEFT JOIN documents=20
> on documents.id=3Dtexts.doc_id WHERE doc_id IN (26194, 2345, 189) AND  =

> (text LIKE '%RIVER%') ORDER BY array_position(ARRAY[26194, 2345,=20
> 189]::INTEGER[], documents.id)

No, you are not ordering by documents.id, you are ordering by an =
expression into which you are passing the documents.id value as one of =
its components.

When you use ORDER BY and DISTINCT together you basically are =
short-handing:

SELECT sq.*
FROM (SELECT DISTINCT ...) AS sq
ORDER BY sq.?

If you want to order by something you have to include it exactly in the =
select-list of the inner/distinct query.

In this example, though, you could just "ORDER BY documents.id DESC"...

David J.