Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ef84y-0000VH-AF for pgsql-sql@arkaria.postgresql.org; Fri, 26 Jan 2018 17:46:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ef84x-00047N-Nv for pgsql-sql@arkaria.postgresql.org; Fri, 26 Jan 2018 17:46:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ef7Ib-000403-J8 for pgsql-sql@lists.postgresql.org; Fri, 26 Jan 2018 16:56:21 +0000 Received: from authsmtp80.register.it ([195.110.122.165] helo=authsmtp.register.it) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ef7IX-0002O1-TV for pgsql-sql@lists.postgresql.org; Fri, 26 Jan 2018 16:56:20 +0000 Received: from PARME ([109.190.253.13]) by cmsmtp with ESMTPA id f7INeB8euiyU4f7IPewoYL; Fri, 26 Jan 2018 17:56:16 +0100 X-Rid: olivier.lepretre@noetika.com@109.190.253.13 From: =?UTF-8?Q?Olivier_Lepr=C3=AAtre?= To: =?UTF-8?Q?'Martin_St=C3=B6cker'?= , , "'David G. Johnston'" References: <015201d3960a$a2db0b60$e8912220$@gmail.com> <016401d39615$9b620bd0$d2262370$@gmail.com> In-Reply-To: Subject: RE: nth_value and row_number in a partition Date: Fri, 26 Jan 2018 17:56:04 +0100 Message-ID: <004e01d396c6$907e6590$b17b30b0$@maule.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_004F_01D396CE.F242CD90" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQG77s7ltW0bH8Fhn8qzGyplstaMAQHCKlo9Ac3YeKAB/y9JjKOJNVQg Content-Language: fr X-Antivirus: Avast (VPS 180126-2, 26/01/2018), Outbound message X-Antivirus-Status: Clean X-CMAE-Envelope: MS4wfK9ObiUh80CtXFS4K0p0MFtCG6f33hxzCJUAb/5eY71rH91tXdG5QxCNM1KqdVrEXAyeou7nE1XKXIqEKDqXS4Jrr45KQFZNikSSiTh6uSOcHq7jzKOE xyjVw+eKIyf9/B11Oup/IQcR/zzwKrGCSxU+jUhl8CQQFYM/bmkEeDy/fnfO0xfo/6+1AFSqCY3zsdgNWDq6lUJv0iCAWEDchaM8FzeTCn9K9dbi1GRrj6n7 Z5IKh5cKBQuLKcir26Ane4emY60/BXvbSiKlaAWNrPA= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_004F_01D396CE.F242CD90 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable David, Martin, thanks for your kind help everything works now ! =20 Nice week-end to all of you =20 Olivier =20 De : Martin St=C3=B6cker [mailto:martin.stoecker@stb-datenservice.de]=20 Envoy=C3=A9 : jeudi 25 janvier 2018 21:13 =C3=80 : Olivier Lepr=C3=AAtre ; = pgsql-sql@lists.postgresql.org Objet : Re: nth_value and row_number in a partition =20 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=C3=AAtre: Hi David, =20 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 =20 select roads,orders,(first_value(v1)=20 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=20 from mytable or select roads,orders,(first_value(v1)=20 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=20 from mytable =20 >>syntax error near order (bold) =20 select roads,orders,(first_value(v1)=20 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=20 from mytable =20 >> syntax error near over =20 select roads,orders,(first_value(v1)=20 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=20 from mytable =20 >> window call cannot be imbricated =20 select roads,orders,(first_value(v1)=20 over (partition by roads,segment order by = orders)-nth_value(v2,row_number() over (partition by roads,segment = order by orders)::integer)) as result=20 from mytable =20 >> nth_value requires an over clause =20 select roads,orders,(first_value(v1)=20 over (partition by roads,segment order by = orders)-nth_value(v2,row_number()::integer) over (partition by = roads,segment order by orders)) as result=20 from mytable =20 >> row_number requires an over clause =20 =20 =20 =20 =20 De : David G. Johnston [mailto:david.g.johnston@gmail.com]=20 Envoy=C3=A9 : jeudi 25 janvier 2018 19:44 =C3=80 : Olivier Lepr=C3=AAtre = Cc : pgsql-sql@lists.postgresql.org = =20 Objet : Re: nth_value and row_number in a partition =20 On Thursday, January 25, 2018, Olivier Lepr=C3=AAtre = > wrote: nth_value(integer, bigint) doesn't exists. =20 This is close, you just need to cast to integer.=20 (cast(row_number() as integer) over (partition by roads,segments order = by orders)))=20 You cannot separate the window function from its over clause. =20 Cast( Row_number() over (...) as integer ) =20 Not tested...and I tend to use :: instead of cast =20 David J. =20 ------=_NextPart_000_004F_01D396CE.F242CD90 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

David, Martin, = thanks for your kind help everything works now !

 

Nice week-end to = all of you

 

Olivier<= /span>

 

De : Martin St=C3=B6cker = [mailto:martin.stoecker@stb-datenservice.de] =
Envoy=C3=A9 : jeudi 25 janvier 2018 = 21:13
=C3=80 : Olivier Lepr=C3=AAtre = <o.lepretre@gmail.com>; = pgsql-sql@lists.postgresql.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=C3=AAtre:

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.g.johnston@gmail.= com]
Envoy=C3=A9 : jeudi 25 janvier 2018 = 19:44
=C3=80 : Olivier Lepr=C3=AAtre <o.lepretre@gmail.com>
= Cc : pgsql-sql@lists.postgresql= .org
Objet : Re: nth_value and row_number in a = partition

 

On Thursday, January 25, 2018, Olivier Lepr=C3=AAtre = <o.lepretre@gmail.com> = wrote:

nth_value(in= teger, 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.

 

------=_NextPart_000_004F_01D396CE.F242CD90--