Re: Using 'WITH SELECT' Results Do Not Match 'SELECT FROM <table>' Results

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 containing the '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' required by '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. However I 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(dot)klaver(at)aklaver(dot)com