Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eemIh-00067k-07 for pgsql-sql@arkaria.postgresql.org; Thu, 25 Jan 2018 18:31:03 +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 1eemIg-0002Zt-Dt for pgsql-sql@arkaria.postgresql.org; Thu, 25 Jan 2018 18:31:02 +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 1eemIg-0002Zk-4x for pgsql-sql@lists.postgresql.org; Thu, 25 Jan 2018 18:31:02 +0000 Received: from mail-wr0-x229.google.com ([2a00:1450:400c:c0c::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eemIb-00048w-85 for pgsql-sql@lists.postgresql.org; Thu, 25 Jan 2018 18:30:59 +0000 Received: by mail-wr0-x229.google.com with SMTP id f11so8648080wre.4 for ; Thu, 25 Jan 2018 10:30:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:subject:date:message-id:mime-version:thread-index :content-language; bh=7uaxzb6SJ1wEoyDb+OLDXsar+E2PJFqYOzd+2nKO5aI=; b=IzvZVcdPT7vIFco5fF+Z1vzL+GIZ885KGFIv3smyYqzDrnFOVX43w24/BHsoCM/mP3 QG6RBDt9QtrYkytC8lHkB+GmytvqgrrwugR4Lvqjur4yxturQuLW9g7f7EvVlz7JX8/V GeB92X4EomUjXBQcmlpoll8zHEPr8c3CZzbWfebVTvSTj3BsSjD2Bsxn8Lqr2gVQSCd3 mV0gopZr0eytkBi5ZvU/MBxTcUmUsmF3B/YcLGYqYv1aWLYp0wSWJ22QOcPu1S3ZWMJo kKOgRaKJRDk4uBExh+BJ1h5m7LgZ3YLdCAl3MXOdxYGZR6NNDgvfjsi8r2WPwy5UD6cU uNIQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:subject:date:message-id:mime-version :thread-index:content-language; bh=7uaxzb6SJ1wEoyDb+OLDXsar+E2PJFqYOzd+2nKO5aI=; b=NAjqayIFIfPLn9+EthfhZ3w/wa9zb/olmi8Es3cSUyD/YJOnwOPeePBE6tHBCqL4yK bZNuhIn6Ht706P30Yh2UU4WsSMWtbZkWxjvQ9wdwRdfXU7oqYRI/y/gNPQYz2xf9KXt0 SihDIioqQsgMjY4ExjLqVkO+1mCs0/XR8Wn1bgYIPOuIr/uaavqPoDhoWQGaH0n1WEdt ZY78RGp6zIQRiCNIUKnr/4a7cCCVqneEo4HZYpJC14l3FdVEIJ2Rs+O40OELaANO4R6p g557fdJZD5/rnMLzxFAg48md2CsJtJbwj/XW43Z/5eC/fWDg4vUVfjrW+DKexmzrplAM GECA== X-Gm-Message-State: AKwxytew+PJb/MfATPY73DO+KXKeOv3qHLwdb+1HK8dpnhJXKrQeLmhX pWKpRCIZIWcF+ukQKhL+0g8SFw== X-Google-Smtp-Source: AH8x224fD/McEgEoupyEO74tOKZZtFKMpYRqALj5JZbto3/bmNHDTy6oLFhD+0OrGSqtQgj/TObedg== X-Received: by 10.223.183.43 with SMTP id l43mr10293162wre.181.1516905055874; Thu, 25 Jan 2018 10:30:55 -0800 (PST) Received: from PARME (66.228.71.37.rev.sfr.net. [37.71.228.66]) by smtp.gmail.com with ESMTPSA id j17sm2111939wmc.41.2018.01.25.10.30.54 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 25 Jan 2018 10:30:55 -0800 (PST) From: =?iso-8859-1?Q?Olivier_Lepr=EAtre?= To: Subject: nth_value and row_number in a partition Date: Thu, 25 Jan 2018 19:30:53 +0100 Message-ID: <015201d3960a$a2db0b60$e8912220$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0153_01D39613.04A00FA0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AdOWCksuTWt1M9wOSwiBDG5ZcvfyDg== 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_0153_01D39613.04A00FA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, =20 I can't find the right syntax to use simultaneously nth_value and = row_number in a partition. I have a table with roads and their orders, a road segment name and two values v1 and v2 I want to decrement first_value v1 of each partition with the current = record value v2 of the partition in a third column v3 in the same record. So I thought that I could combine and row_number which returns "number of the current row within its partition, counting from 1" with nth_value =20 My problem is that I get either syntax error near over, window function needs an over clause, nth_value(integer, bigint) doesn't exists. Query = works if I just uses something like=20 nth_value(v2,1) but I can't find how I can replace "1" with the current = row position in the partition (1,2,3=85) =20 I tried dozen of syntax with no success. Here is one of them =20 select roads,orders,(first_value(v1) over (partition by roads,segment = order by orders)-(nth_value(v2,(cast(row_number() as integer) over (partition = by roads,segments order by orders))) over (partition by roads,segments = order by orders))) as result from my table =20 Thanks for any help ! =20 Olivier ------=_NextPart_000_0153_01D39613.04A00FA0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi,

 

I can't find the right syntax to = use simultaneously nth_value and row_number in a = partition.

I have a table with roads and their orders, a road segment = name and two values v1 and v2

I want to decrement first_value v1 = of each partition with the current record value v2 of the partition in a = third column v3 in the same record. So I thought that I could combine = and row_number which returns "number of the current row within its partition, counting = from 1" with nth_value

 

My problem is that I get either syntax error near over, = window function needs an over clause, nth_value(integer, bigint) doesn't = exists. Query works if I just uses something like =

nth_value(v2,1) but I can't find how I can replace = "1" with the current row position in the partition = (1,2,3…)

 

I tried dozen of syntax with no success. Here is one of = them

 

select roads,orders,(first_value(v1) over (partition by = roads,segment order by orders)-(nth_value(v2,(cast(row_number() as = integer)=A0 over (partition by roads,segments order by orders))) over = (partition by roads,segments order by orders))) =A0as result from my = table

 

Thanks for any help !

 

Olivier

------=_NextPart_000_0153_01D39613.04A00FA0--