Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eenXe-0002Ix-MD for pgsql-sql@arkaria.postgresql.org; Thu, 25 Jan 2018 19:50:34 +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 1eenXe-0008G4-9L for pgsql-sql@arkaria.postgresql.org; Thu, 25 Jan 2018 19:50:34 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eenXd-0008Fv-OK for pgsql-sql@lists.postgresql.org; Thu, 25 Jan 2018 19:50:34 +0000 Received: from mail-wm0-f44.google.com ([74.125.82.44]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eenXa-0002CO-1u for pgsql-sql@lists.postgresql.org; Thu, 25 Jan 2018 19:50:32 +0000 Received: by mail-wm0-f44.google.com with SMTP id i186so16773249wmi.4 for ; Thu, 25 Jan 2018 11:50:29 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:references:in-reply-to:subject:date:message-id:mime-version :thread-index:content-language; bh=SLZpDtJN66pNLgRZeo5Yf1AbKJdWICutM2wFVv8gDOw=; b=hjethkSXhGxoXQdHaUYDxc7SYIfPUHr1R92MMvpZtse+DxlImkDWct1QGtTl5/22Dq Bp/AXrbjUncHLyPLJZ3h6w18q1ntAkYdhVtQuUuIVpIm8KSPmeWOwFNUp4o7hZvfxuGa U8NKiTaGn6fdWj3ChEsStPWHBr0ch0bgqt5BT1Km/FZKiB4RfkSJLwjOresdetbF7pqH RttNOjQ7llyrtETa/NGIlq9z7BV2jx1sOexi8qcMN9pYQNsoHwqFSG4Fq6XfWXU3HQDQ WIPplpf4YAhr4EYUtGyeleEz4+IQEcWfmDOpvLjAJv+wiXdggVUygr22IdClWHDwmQA3 Kg1Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=SLZpDtJN66pNLgRZeo5Yf1AbKJdWICutM2wFVv8gDOw=; b=fTm9tKAK95o+V8WGUl55p+qOiNd7pcKBrZZmabOlUYi2gbQxcE/vPWyrNG3D/HClLU IOXBfbcqf9NfBgcQYnrWNBXmXbJxLu5a8zEgcU3GzDiJHPql5/zU26ihSlhlmyBBF+7h Yg6VA9Qd2kS5kziyhlh/sb1TT1rXP7yqRF+wb7irc9vbEf/xb6MlLQ679Wqlq9WeI+Cy owHjjQ6m/1UP4TL6Qfs7r4mZ7M79r2T570jWDlrKhVe7IPH0E7KlTzrlktlgTi3E56+U Km0YkcKxNfiUgzNFlq9KO1PU5FlfIecyi1GC2hA9gBDNV2WeeJxZez7gkDuSa/RxmH+d 4doQ== X-Gm-Message-State: AKwxytepK4AlAGTFlOVp9hOlSZTPC6wBUs8GIl4LecqOdYKZbfgjs1Vy 0w8/enL2Iunh/6PeBYNzYAr9Xg== X-Google-Smtp-Source: AH8x224M++ThzdEGuuRuP0oVBXJI2vAtKPuEi+5KBCHZm70D1cY7BL2EafQq7hbjZQiMk9yxxTQyhg== X-Received: by 10.28.159.7 with SMTP id i7mr9224523wme.57.1516909767728; Thu, 25 Jan 2018 11:49:27 -0800 (PST) Received: from PARME (66.228.71.37.rev.sfr.net. [37.71.228.66]) by smtp.gmail.com with ESMTPSA id l1sm2046899wmh.13.2018.01.25.11.49.26 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 25 Jan 2018 11:49:27 -0800 (PST) From: =?utf-8?Q?Olivier_Lepr=C3=AAtre?= To: References: <015201d3960a$a2db0b60$e8912220$@gmail.com> In-Reply-To: Subject: RE: nth_value and row_number in a partition Date: Thu, 25 Jan 2018 20:49:25 +0100 Message-ID: <016401d39615$9b620bd0$d2262370$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0165_01D3961D.FD2A9280" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQG77s7ltW0bH8Fhn8qzGyplstaMAQHCKlo9o6Y3rxA= Content-Language: fr X-Antivirus: Avast (VPS 180125-2, 25/01/2018), Outbound message X-Antivirus-Status: Clean List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0165_01D3961D.FD2A9280 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable 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 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. ------=_NextPart_000_0165_01D3961D.FD2A9280 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

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)=C2=A0 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()=C2=A0 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_0165_01D3961D.FD2A9280--