agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedSelect Distinct Order By Array_Position
7+ messages / 3 participants
[nested] [flat]
* Select Distinct Order By Array_Position
@ 2018-11-26 19:12 Mark Williams <markwillimas@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Mark Williams @ 2018-11-26 19:12 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
__
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Select Distinct Order By Array_Position
@ 2018-11-26 19:20 Rob Sargent <robjsargent@gmail.com>
parent: Mark Williams <markwillimas@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Rob Sargent @ 2018-11-26 19:20 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
> On Nov 26, 2018, at 12:12 PM, Mark Williams <markwillimas@gmail.com> wrote:
>
> Hi,
>
> I am getting an error “SELECT DISTINCT, ORDER BY expressions must appear in select list”. I am ordering by documents.id <http://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 <http://documents.id/;, page_no FROM texts LEFT JOIN documents on documents.id <http://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 <http://documents.id/;)
>
> Thanks,
>
> Mark
> __
Try put the array_position clause in the select and add documents.id <http://documents.id/; to the order by?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Select Distinct Order By Array_Position
@ 2018-11-26 19:46 David G. Johnston <david.g.johnston@gmail.com>
parent: Mark Williams <markwillimas@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2018-11-26 19:46 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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 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)
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Select Distinct Order By Array_Position
@ 2018-11-26 19:58 Mark Williams <markwillimas@gmail.com>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Mark Williams @ 2018-11-26 19:58 UTC (permalink / raw)
To: 'Rob Sargent' <robjsargent@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
Wasn’t 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 <mailto:markwillimas@gmail.com> > wrote:
Hi,
I am getting an error “SELECT DISTINCT, ORDER BY expressions must appear in select list”. I am ordering by <http://documents.id/; 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 <http://documents.id/; documents.id, page_no FROM texts LEFT JOIN documents on <http://documents.id/; 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[], <http://documents.id/; documents.id)
Thanks,
Mark
__
Try put the array_position clause in the select and add documents.id <http://documents.id; to the order by?
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Select Distinct Order By Array_Position
@ 2018-11-26 19:59 Mark Williams <markwillimas@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Mark Williams @ 2018-11-26 19:59 UTC (permalink / raw)
To: 'David G. Johnston' <david.g.johnston@gmail.com>; +Cc: 'pgsql-sql' <pgsql-sql@lists.postgresql.org>
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>
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
> 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)
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Select Distinct Order By Array_Position
@ 2018-11-26 20:06 David G. Johnston <david.g.johnston@gmail.com>
parent: Mark Williams <markwillimas@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2018-11-26 20:06 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
On Mon, Nov 26, 2018 at 12:58 PM Mark Williams <markwillimas@gmail.com> wrote:
>
> Wasn’t aware it was possible to put array_position statement in the actual select or is this a select within a select?
Easy enough to try either way
> 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?
Not sure what the question is...
Anyway, maybe this will be helpful:
SELECT vals.*
FROM (VALUES (1::integer),(2),(3),(4),(5)) vals (v),
LATERAL unnest(ARRAY[3,1,5]::integer[]) with ordinality AS spec (s, o)
WHERE v = s
ORDER BY o
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Select Distinct Order By Array_Position
@ 2018-11-26 20:22 Mark Williams <markwillimas@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Mark Williams @ 2018-11-26 20:22 UTC (permalink / raw)
To: 'David G. Johnston' <david.g.johnston@gmail.com>; +Cc: 'Rob Sargent' <robjsargent@gmail.com>; 'pgsql-sql' <pgsql-sql@lists.postgresql.org>
Many thanks. I'll mess around with what you've sent and see if I can get it working.
__
-----Original Message-----
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: 26 November 2018 20:07
To: Mark Williams <markwillimas@gmail.com>
Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Select Distinct Order By Array_Position
On Mon, Nov 26, 2018 at 12:58 PM Mark Williams <markwillimas@gmail.com> wrote:
>
> Wasn’t aware it was possible to put array_position statement in the actual select or is this a select within a select?
Easy enough to try either way
> 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?
Not sure what the question is...
Anyway, maybe this will be helpful:
SELECT vals.*
FROM (VALUES (1::integer),(2),(3),(4),(5)) vals (v), LATERAL unnest(ARRAY[3,1,5]::integer[]) with ordinality AS spec (s, o) WHERE v = s ORDER BY o
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-11-26 20:22 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-26 19:12 Select Distinct Order By Array_Position Mark Williams <markwillimas@gmail.com>
2018-11-26 19:20 ` Rob Sargent <robjsargent@gmail.com>
2018-11-26 19:58 ` Mark Williams <markwillimas@gmail.com>
2018-11-26 20:06 ` David G. Johnston <david.g.johnston@gmail.com>
2018-11-26 20:22 ` Mark Williams <markwillimas@gmail.com>
2018-11-26 19:46 ` David G. Johnston <david.g.johnston@gmail.com>
2018-11-26 19:59 ` Mark Williams <markwillimas@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox