<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="window-functions-named-windows">
      </a>
      14.20.4 Named Windows
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045191776896">
  </a>
  <a class="indexterm" name="idm46045191775408">
  </a>
  <p>
   Windows can be defined and given names by which to refer to them
        in
   <code class="literal">
    OVER
   </code>
   clauses. To do this, use a
   <code class="literal">
    WINDOW
   </code>
   clause. If present in a query, the
   <code class="literal">
    WINDOW
   </code>
   clause falls between the positions of
        the
   <code class="literal">
    HAVING
   </code>
   and
   <code class="literal">
    ORDER BY
   </code>
   clauses, and has this syntax:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa32405680"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">WINDOW</span> <em class="replaceable">window_name</em> <span class="token keyword">AS</span> <span class="token punctuation">(</span><em class="replaceable">window_spec</em><span class="token punctuation">)</span>
    <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">window_name</em> <span class="token keyword">AS</span> <span class="token punctuation">(</span><em class="replaceable">window_spec</em><span class="token punctuation">)</span><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   For each window definition,
   <em class="replaceable">
    <code>
     window_name
    </code>
   </em>
   is the window name, and
   <em class="replaceable">
    <code>
     window_spec
    </code>
   </em>
   is the same type of
        window specification as given between the parentheses of an
   <code class="literal">
    OVER
   </code>
   clause, as described in
   <a class="xref" href="window-functions-usage.html" title="14.20.2 Window Function Concepts and Syntax">
    Section 14.20.2, “Window Function Concepts and Syntax”
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa18744139"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><em class="replaceable">window_spec</em>:
    <span class="token punctuation">[</span><em class="replaceable">window_name</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">partition_clause</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">order_clause</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">frame_clause</em><span class="token punctuation">]</span></code></pre>
  </div>
  <p>
   A
   <code class="literal">
    WINDOW
   </code>
   clause is useful for queries in
        which multiple
   <code class="literal">
    OVER
   </code>
   clauses would otherwise
        define the same window. Instead, you can define the window once,
        give it a name, and refer to the name in the
   <code class="literal">
    OVER
   </code>
   clauses. Consider this query, which
        defines the same window multiple times:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75536678"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span>
  val<span class="token punctuation">,</span>
  <span class="token function">ROW_NUMBER</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">OVER</span> <span class="token punctuation">(</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> val<span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'row_number'</span><span class="token punctuation">,</span>
  <span class="token function">RANK</span><span class="token punctuation">(</span><span class="token punctuation">)</span>       <span class="token keyword">OVER</span> <span class="token punctuation">(</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> val<span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'rank'</span><span class="token punctuation">,</span>
  <span class="token function">DENSE_RANK</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">OVER</span> <span class="token punctuation">(</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> val<span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'dense_rank'</span>
<span class="token keyword">FROM</span> numbers<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The query can be written more simply by using
   <code class="literal">
    WINDOW
   </code>
   to define the window once and
        referring to the window by name in the
   <code class="literal">
    OVER
   </code>
   clauses:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66633455"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span>
  val<span class="token punctuation">,</span>
  <span class="token function">ROW_NUMBER</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'row_number'</span><span class="token punctuation">,</span>
  <span class="token function">RANK</span><span class="token punctuation">(</span><span class="token punctuation">)</span>       <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'rank'</span><span class="token punctuation">,</span>
  <span class="token function">DENSE_RANK</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'dense_rank'</span>
<span class="token keyword">FROM</span> numbers
<span class="token keyword">WINDOW</span> w <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> val<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   A named window also makes it easier to experiment with the
        window definition to see the effect on query results. You need
        only modify the window definition in the
   <code class="literal">
    WINDOW
   </code>
   clause, rather than multiple
   <code class="literal">
    OVER
   </code>
   clause definitions.
  </p>
  <p>
   If an
   <code class="literal">
    OVER
   </code>
   clause uses
   <code class="literal">
    OVER
        (
    <em class="replaceable">
     <code>
      window_name
     </code>
    </em>
    ...)
   </code>
   rather
        than
   <code class="literal">
    OVER
    <em class="replaceable">
     <code>
      window_name
     </code>
    </em>
   </code>
   , the named
        window can be modified by the addition of other clauses. For
        example, this query defines a window that includes partitioning,
        and uses
   <code class="literal">
    ORDER BY
   </code>
   in the
   <code class="literal">
    OVER
   </code>
   clauses to modify the window in
        different ways:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa21721618"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span>
  <span class="token keyword">DISTINCT</span> <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span>
  <span class="token function">FIRST_VALUE</span><span class="token punctuation">(</span><span class="token datatype">year</span><span class="token punctuation">)</span> <span class="token keyword">OVER</span> <span class="token punctuation">(</span>w <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token datatype">year</span> <span class="token keyword">ASC</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">first</span><span class="token punctuation">,</span>
  <span class="token function">FIRST_VALUE</span><span class="token punctuation">(</span><span class="token datatype">year</span><span class="token punctuation">)</span> <span class="token keyword">OVER</span> <span class="token punctuation">(</span>w <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token datatype">year</span> <span class="token keyword">DESC</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">last</span>
<span class="token keyword">FROM</span> sales
<span class="token keyword">WINDOW</span> w <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> country<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   An
   <code class="literal">
    OVER
   </code>
   clause can only add properties to a
        named window, not modify them. If the named window definition
        includes a partitioning, ordering, or framing property, the
   <code class="literal">
    OVER
   </code>
   clause that refers to the window name
        cannot also include the same kind of property or an error
        occurs:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      This construct is permitted because the window definition
            and the referring
      <code class="literal">
       OVER
      </code>
      clause do not
            contain the same kind of properties:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42050195"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">OVER</span> <span class="token punctuation">(</span>w <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> country<span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WINDOW</span> w <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> country<span class="token punctuation">)</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      This construct is not permitted because the
      <code class="literal">
       OVER
      </code>
      clause specifies
      <code class="literal">
       PARTITION
            BY
      </code>
      for a named window that already has
      <code class="literal">
       PARTITION BY
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7991103"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">OVER</span> <span class="token punctuation">(</span>w <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token datatype">year</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WINDOW</span> w <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> country<span class="token punctuation">)</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   The definition of a named window can itself begin with a
   <em class="replaceable">
    <code>
     window_name
    </code>
   </em>
   . In such cases, forward
        and backward references are permitted, but not cycles:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      This is permitted; it contains forward and backward
            references but no cycles:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa61675262"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">WINDOW</span> w1 <span class="token keyword">AS</span> <span class="token punctuation">(</span>w2<span class="token punctuation">)</span><span class="token punctuation">,</span> w2 <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> w3 <span class="token keyword">AS</span> <span class="token punctuation">(</span>w1<span class="token punctuation">)</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      This is not permitted because it contains a cycle:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa94529933"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">WINDOW</span> w1 <span class="token keyword">AS</span> <span class="token punctuation">(</span>w2<span class="token punctuation">)</span><span class="token punctuation">,</span> w2 <span class="token keyword">AS</span> <span class="token punctuation">(</span>w3<span class="token punctuation">)</span><span class="token punctuation">,</span> w3 <span class="token keyword">AS</span> <span class="token punctuation">(</span>w1<span class="token punctuation">)</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
