Re: How to write such a query

Ken,

On Fri, Sep 18, 2020 at 3:35 PM Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> wrote:

> On Fri, Sep 18, 2020 at 1:26 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
>
>> On 9/18/20 3:18 PM, Igor Korot wrote:
>>
> Thank you for the info.
>> My problem is that I want to emulate Access behavior.
>>
>> As I said - Access does it without changing the query internally (I
>> presume).
>>
>> I want to do the same with PostgreSQL.
>>
>> I'm just trying to understand how to make it work for any query
>>
>> I can have 3,4,5 tables, query them and then update the Nth record in the
>> resulting recordset.
>>
>> Access does it, PowerBuilder does it.
>>
>> I just want to understand how.
>>
>>
>> They do it by hiding the details from you.
>>
>>
> That's true. And Igor--people are asking you some good questions about
> why and design and such that you'd probably be well-advised to think about
> and respond to.
>
> So I'm not saying you should do this, but responding to your question
> specifically, and what the "details" are that Ron alludes to, one way to
> get the result you're asking about is to run your query adding on row
> numbers (pay attention to your ordering!), and then reference that result
> set from an update to get the primary key you want. So I didn't test it,
> but something roughly like this:
>
> WITH tmp AS (SELECT X.field1, Y.field2,row_number() OVER () from X, Y
> WHERE X.id = Y.id ) UPDATE x SET ... FROM tmp WHERE
> tmp.row_number=5 AND x.field1=tmp.field1;
>

I didn't know that row_number() function exists and it is available across
different DBMSes.

I will test that query later.

Thank you.

Now one other little thing: could you point me to the documentation that
explains the meaning of the "window function"?

> Cheers,
> Ken
>
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ <http://agency-software.org/>*
> *https://demo.agency-software.org/client
> <https://demo.agency-software.org/client>*
> ken(dot)tanzer(at)agency-software(dot)org
> (253) 245-3801
>
> Subscribe to the mailing list
> <agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.
>