Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eentG-0003Zo-UH for pgsql-sql@arkaria.postgresql.org; Thu, 25 Jan 2018 20:12:55 +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 1eentG-00069n-0s for pgsql-sql@arkaria.postgresql.org; Thu, 25 Jan 2018 20:12:54 +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 1eentF-00069e-H8 for pgsql-sql@lists.postgresql.org; Thu, 25 Jan 2018 20:12:53 +0000 Received: from mail.inqbus.de ([164.177.165.236]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eent7-0002eS-WA for pgsql-sql@lists.postgresql.org; Thu, 25 Jan 2018 20:12:52 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=stb-datenservice.de; s=20160215; h=Content-Type:In-Reply-To:MIME-Version: Date:Message-ID:From:References:To:Subject:Sender:Reply-To:Cc: Content-Transfer-Encoding:Content-ID:Content-Description:Resent-Date: Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID:List-Id: List-Help:List-Unsubscribe:List-Subscribe:List-Post:List-Owner:List-Archive; bh=nUwExVp9h40XJhRp6fB4cgwE7Q9HxEgh6yr9yW6Q2sI=; b=yNatbaXnsOaY5dHqTJ8PTZBGD zON335NqLX8lbzSL0BeY9g0K0yNfZsQl2qFAVRPFGdrHy95AXTABcMaZuKtwejKY1pj3jRX139Vqy NXkCuH0WVcv3T0/agkfKD2hpDPqClXs3l2TjPDPtOFSDeaLM66JDBbNbrxI8UrHRYLKjU=; Received: from x4db4923d.dyn.telefonica.de ([77.180.146.61]:57394 helo=[192.168.178.20]) by mail.inqbus.de with esmtpa (Exim 4.88) (envelope-from ) id 1eent1-0000k9-Pc; Thu, 25 Jan 2018 21:12:42 +0100 Subject: Re: nth_value and row_number in a partition To: =?UTF-8?Q?Olivier_Lepr=c3=aatre?= , pgsql-sql@lists.postgresql.org References: <015201d3960a$a2db0b60$e8912220$@gmail.com> <016401d39615$9b620bd0$d2262370$@gmail.com> From: =?UTF-8?Q?Martin_St=c3=b6cker?= Message-ID: Date: Thu, 25 Jan 2018 21:12:39 +0100 User-Agent: Mozilla/5.0 (Windows NT 6.0; rv:52.0) Gecko/20100101 Thunderbird/52.5.2 MIME-Version: 1.0 In-Reply-To: <016401d39615$9b620bd0$d2262370$@gmail.com> Content-Type: multipart/alternative; boundary="------------AF3852F5BBF5E9F86FA4DCFB" Content-Language: de List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------AF3852F5BBF5E9F86FA4DCFB Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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.g.johnston@gmail.com] > *Envoyé :* jeudi 25 janvier 2018 19:44 > *À :* Olivier Leprêtre > *Cc :* pgsql-sql@lists.postgresql.org > *Objet :* Re: nth_value and row_number in a partition > > On Thursday, January 25, 2018, Olivier Leprêtre > 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. > --------------AF3852F5BBF5E9F86FA4DCFB Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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.g.johnston@gmail.com]
Envoyé : jeudi 25 janvier 2018 19:44
À : Olivier Leprêtre <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être <o.lepretre@gmail.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.


--------------AF3852F5BBF5E9F86FA4DCFB--