[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: nth_value and row_number in a partition
From: Martin_Stöcker <martin.stoecker () stb-datenservice ! de>
Date: 2018-01-26 17:04:18
Message-ID: f9ba2fa5-2efe-3426-173e-c3c03eaf3659 () stb-datenservice ! de
[Download RAW message or body]
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.stoecker@stb-datenservice.de]
> *Envoyé :* jeudi 25 janvier 2018 21:13
> *À :* Olivier Leprêtre <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ê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>
> <mailto:o.lepretre@gmail.com>
> *Cc :* pgsql-sql@lists.postgresql.org
> <mailto: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 <mailto: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.
>
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
same to you<br>
it's always a pleasure to help<br>
<br>
<div class="moz-cite-prefix">Am 26.01.2018 um 17:56 schrieb Olivier
Leprêtre:<br>
</div>
<blockquote type="cite"
cite="mid:004e01d396c6$907e6590$b17b30b0$@maule.fr">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
color:black;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
color:black;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span
style="color:windowtext;mso-fareast-language:EN-US"
lang="EN-GB">David, Martin, thanks for your kind help
everything works now !<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="color:windowtext;mso-fareast-language:EN-US"
lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="color:windowtext;mso-fareast-language:EN-US"
lang="EN-GB">Nice week-end to all of you<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="color:windowtext;mso-fareast-language:EN-US"
lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="color:windowtext;mso-fareast-language:EN-US"
lang="EN-GB">Olivier<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="color:windowtext;mso-fareast-language:EN-US"
lang="EN-GB"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span style="color:windowtext">De \
:</span></b><span style="color:windowtext"> Martin Stöcker
[<a class="moz-txt-link-freetext" \
href="mailto:martin.stoecker@stb-datenservice.de">mailto:martin.stoecker@stb-datenservice.de</a>] \
<br> <b>Envoyé :</b> jeudi 25 janvier 2018 21:13<br>
<b>À :</b> Olivier Leprêtre
<a class="moz-txt-link-rfc2396E" \
href="mailto:o.lepretre@gmail.com"><o.lepretre@gmail.com></a>;
<a class="moz-txt-link-abbreviated" \
href="mailto:pgsql-sql@lists.postgresql.org">pgsql-sql@lists.postgresql.org</a><br> \
<b>Objet :</b> Re: nth_value and row_number in a partition<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal" style="margin-bottom:12.0pt">Hi Olivier<br>
<br>
can you please give me the structure of your table, maybee
some sample data too.<br>
And please describe in words not in SQL your calculation.<br>
<br>
Regards Martin<o:p></o:p></p>
<div>
<p class="MsoNormal">Am 25.01.2018 um 20:49 schrieb Olivier
Leprêtre:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">Hi David,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">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</span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">select roads,orders,(first_value(v1) \
</span><o:p></o:p></p> <p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">over (partition by roads,segment order by
orders)-(nth_value(v2,cast(row_number() over (partition by
roads,<b>order </b>by orders) as integer)) over
(partition by roads,segment order by orders))) as result \
</span><o:p></o:p></p> <p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">from mytable</span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">or</span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">select roads,orders,(first_value(v1) \
</span><o:p></o:p></p> <p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">over (partition by roads,segment order by
orders)-(nth_value(v2,row_number() over (partition by
roads,<b>order</b> by orders)::integer)) over (partition
by roads,segment order by orders))) as result </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">from mytable</span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">>>syntax error near order \
(bold)</span><o:p></o:p></p> <p class="MsoNormal"><span \
style="mso-fareast-language:EN-US" lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">select roads,orders,(first_value(v1) \
</span><o:p></o:p></p> <p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">over (partition by roads,segment order by
orders)-(nth_value(v2,row_number() over (partition by
roads)::integer)) <b>over</b> (partition by roads,segment
order by orders))) as result </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">from mytable</span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">>> syntax error near over</span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">select roads,orders,(first_value(v1) \
</span><o:p></o:p></p> <p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">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 </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">from mytable</span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">>> window call cannot be \
imbricated</span><o:p></o:p></p> <p class="MsoNormal"><span \
style="mso-fareast-language:EN-US" lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">select roads,orders,(first_value(v1) \
</span><o:p></o:p></p> <p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">over (partition by roads,segment order by
orders)-nth_value(v2,row_number() over (partition by
roads,segment order by orders)::integer)) as result \
</span><o:p></o:p></p> <p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">from mytable</span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">>> nth_value requires an over \
clause</span><o:p></o:p></p> <p class="MsoNormal"><span \
style="mso-fareast-language:EN-US" lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">select roads,orders,(first_value(v1) \
</span><o:p></o:p></p> <p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">over (partition by roads,segment order by
orders)-nth_value(v2,row_number()::integer) over
(partition by roads,segment order by orders)) as result \
</span><o:p></o:p></p> <p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">from mytable</span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB">>> row_number requires an over \
clause</span><o:p></o:p></p> <p class="MsoNormal"><span \
style="mso-fareast-language:EN-US" lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"
lang="EN-GB"> </span><o:p></o:p></p>
<p class="MsoNormal"><b>De :</b> David G. Johnston [<a
href="mailto:david.g.johnston@gmail.com"
moz-do-not-send="true">mailto:david.g.johnston@gmail.com</a>]
<br>
<b>Envoyé :</b> jeudi 25 janvier 2018 19:44<br>
<b>À :</b> Olivier Leprêtre <a
href="mailto:o.lepretre@gmail.com" \
moz-do-not-send="true"><o.lepretre@gmail.com></a><br> <b>Cc :</b> <a \
href="mailto:pgsql-sql@lists.postgresql.org"
moz-do-not-send="true">pgsql-sql@lists.postgresql.org</a><br>
<b>Objet :</b> Re: nth_value and row_number in a partition<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">On Thursday, January 25, 2018, Olivier
Leprêtre <<a href="mailto:o.lepretre@gmail.com"
moz-do-not-send="true">o.lepretre@gmail.com</a>> \
wrote:<o:p></o:p></p> <blockquote style="border:none;border-left:solid #CCCCCC
1.0pt;padding:0cm 0cm 0cm
6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt">
<div>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">nth_value(integer,
bigint) doesn't exists. <o:p></o:p></p>
</div>
</blockquote>
<div>
<p class="MsoNormal">This is close, you just need to cast to
integer. <o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC
1.0pt;padding:0cm 0cm 0cm
6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt">
<div>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
lang="EN-GB">(cast(row_number() as integer) over
(partition by roads,segments order by orders))) \
</span><o:p></o:p></p> </div>
</blockquote>
<div>
<p class="MsoNormal">You cannot separate the window function
from its over clause.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Cast( Row_number() over (...) as
integer )<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Not tested...and I tend to use ::
instead of cast<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">David J.<o:p></o:p></p>
</div>
</blockquote>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</blockquote>
<br>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic