From: | Martin Stöcker <martin(dot)stoecker(at)stb-datenservice(dot)de> |
---|---|
To: | Olivier Leprêtre <olepretre(at)maule(dot)fr>, pgsql-sql(at)lists(dot)postgresql(dot)org, "'David G(dot) Johnston'" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Subject: | Re: nth_value and row_number in a partition |
Date: | 2018-01-26 17:04:18 |
Message-ID: | f9ba2fa5-2efe-3426-173e-c3c03eaf3659@stb-datenservice.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
same to you
it's always a pleasure to help
Am 26.01.2018 um 17:56 schrieb Olivier Leprêtre:
>
> David, Martin, thanks for your kind help everything works now !
>
> Nice week-end to all of you
>
> Olivier
>
> *De :*Martin Stöcker [mailto:martin(dot)stoecker(at)stb-datenservice(dot)de]
> *Envoyé :* jeudi 25 janvier 2018 21:13
> *À :* Olivier Leprêtre <o(dot)lepretre(at)gmail(dot)com>;
> pgsql-sql(at)lists(dot)postgresql(dot)org
> *Objet :* Re: nth_value and row_number in a partition
>
> Hi Olivier
>
> can you please give me the structure of your table, maybee some sample
> data too.
> And please describe in words not in SQL your calculation.
>
> Regards Martin
>
> Am 25.01.2018 um 20:49 schrieb Olivier Leprêtre:
>
> Hi David,
>
> Thanks for your answer, I tried your suggestion as well as many
> other combinations, no success. Here are some of them. I just
> don't understand which syntax is required
>
> select roads,orders,(first_value(v1)
>
> over (partition by roads,segment order by
> orders)-(nth_value(v2,cast(row_number() over (partition by
> roads,*order *by orders) as integer)) over (partition by
> roads,segment order by orders))) as result
>
> from mytable
>
> or
>
> select roads,orders,(first_value(v1)
>
> over (partition by roads,segment order by
> orders)-(nth_value(v2,row_number() over (partition by
> roads,*order* by orders)::integer)) over (partition by
> roads,segment order by orders))) as result
>
> from mytable
>
> >>syntax error near order (bold)
>
> select roads,orders,(first_value(v1)
>
> over (partition by roads,segment order by
> orders)-(nth_value(v2,row_number() over (partition by
> roads)::integer)) *over* (partition by roads,segment order by
> orders))) as result
>
> from mytable
>
> >> syntax error near over
>
> select roads,orders,(first_value(v1)
>
> over (partition by roads,segment order by
> orders)-nth_value(v2,row_number() over (partition by
> roads)::integer) over (partition by roads,segment order by
> orders)) as result
>
> from mytable
>
> >> window call cannot be imbricated
>
> select roads,orders,(first_value(v1)
>
> over (partition by roads,segment order by
> orders)-nth_value(v2,row_number() over (partition by
> roads,segment order by orders)::integer)) as result
>
> from mytable
>
> >> nth_value requires an over clause
>
> select roads,orders,(first_value(v1)
>
> over (partition by roads,segment order by
> orders)-nth_value(v2,row_number()::integer) over (partition by
> roads,segment order by orders)) as result
>
> from mytable
>
> >> row_number requires an over clause
>
> *De :* David G. Johnston [mailto:david(dot)g(dot)johnston(at)gmail(dot)com]
> *Envoyé :* jeudi 25 janvier 2018 19:44
> *À :* Olivier Leprêtre <o(dot)lepretre(at)gmail(dot)com>
> <mailto:o(dot)lepretre(at)gmail(dot)com>
> *Cc :* pgsql-sql(at)lists(dot)postgresql(dot)org
> <mailto:pgsql-sql(at)lists(dot)postgresql(dot)org>
> *Objet :* Re: nth_value and row_number in a partition
>
> On Thursday, January 25, 2018, Olivier Leprêtre
> <o(dot)lepretre(at)gmail(dot)com <mailto:o(dot)lepretre(at)gmail(dot)com>> wrote:
>
> nth_value(integer, bigint) doesn't exists.
>
> This is close, you just need to cast to integer.
>
> (cast(row_number() as integer) over (partition by
> roads,segments order by orders)))
>
> You cannot separate the window function from its over clause.
>
> Cast( Row_number() over (...) as integer )
>
> Not tested...and I tend to use :: instead of cast
>
> David J.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Olivier Leprêtre | 2018-02-02 09:00:28 | search inside partitions |
Previous Message | Olivier Leprêtre | 2018-01-26 16:56:04 | RE: nth_value and row_number in a partition |