Request a Demo
Home   >   mailing lists

Re: [GENERAL] Using 'WITH SELECT' Results Do Not Match 'SELECT FROM' Results - Mailing list pgsql-general
From Adrian Klaver
Subject Re: [GENERAL] Using 'WITH SELECT' Results Do Not Match 'SELECT FROM' Results
Date
Msg-id f71808b6-3132-7fdc-eed1-f46021ee3c7b@aklaver.com
Whole thread Raw
In response to [GENERAL] Text search dictionary vs. the C locale  (twoflower <standa.kurik@gmail.com>)
Responses Re: [GENERAL] Using 'WITH SELECT' Results Do Not Match 'SELECT FROM ' Results
List pgsql-general
Tree view 
On 07/03/2017 05:20 PM, Jerry Regan wrote:
> Adrian,
>
> Thank you for your reply!
>
> I apologize in advance for not being detailed below. Hard to do from my phone.
>
> I did have to move the 'ORDER BY', but not outside the 'WITH'. My first workaround parenthesized the select
containingthe 'ORDER BY', forcing it to be evaluated before the 'INSERT'. That worked. 

Not sure where the INSERT comes into the picture, but glad you got it
working.

>
> But I never liked using a sequence for the c_id column. And using the sequence on my personal workstation was maybe
safe,but given that sequences not are guaranteed to be without gaps, that was not very portable. 

Yeah, that concerned me also, still I figured one problem at a time.

>
> So I searched a bit and found I could use 'row_number()' instead. That approach allowed me to use the 'ORDER BY'
requiredby 'row_number()'. 
>
> That worked and is far more portable to other postgresql instances.
>
> I really do appreciate your response. It is also my nature to continue my research even after asking for help.
HoweverI find my answer, one validates the other. 

Sometimes it just a matter a little push to get out of the rut:)

>
> Thanks again!
>
> /s/jr
> Sent from my iPhone


--
Adrian Klaver
adrian.klaver@aklaver.com

pgsql-general by date:

Previous
From: Jerry Regan
Date:
Subject: Re: [GENERAL] Using 'WITH SELECT' Results Do Not Match 'SELECT FROM ' Results
Next
From: Jerry Regan
Date:
Subject: Re: [GENERAL] Using 'WITH SELECT' Results Do Not Match 'SELECT FROM
' Results