<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="window-functions-usage">
      </a>
      14.20.2 Window Function Concepts and Syntax
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045192027104">
  </a>
  <p>
   This section describes how to use window functions. Examples use
        the same sales information data set as found in the discussion
        of the
   <a class="link" href="miscellaneous-functions.html#function_grouping">
    <code class="literal">
     GROUPING()
    </code>
   </a>
   function in
   <a class="xref" href="group-by-modifiers.html" title="14.19.2 GROUP BY Modifiers">
    Section 14.19.2, “GROUP BY Modifiers”
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72935136"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> sales <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> country<span class="token punctuation">,</span> <span class="token datatype">year</span><span class="token punctuation">,</span> product<span class="token punctuation">;</span>
<span class="token output"><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><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><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><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><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><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><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><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></span>
<span class="token output"><span class="token punctuation">|</span> year <span class="token punctuation">|</span> country <span class="token punctuation">|</span> product    <span class="token punctuation">|</span> profit <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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><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><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><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><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><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></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Phone      <span class="token punctuation">|</span>    100 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Phone      <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     75 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     75 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1200 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     75 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     50 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1200 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> TV         <span class="token punctuation">|</span>    150 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> TV         <span class="token punctuation">|</span>    100 <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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><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><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><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><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><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></span></code></pre>
  </div>
  <a class="indexterm" name="idm46045192021408">
  </a>
  <a class="indexterm" name="idm46045192019920">
  </a>
  <p>
   A window function performs an aggregate-like operation on a set
        of query rows. However, whereas an aggregate operation groups
        query rows into a single result row, a window function produces
        a result for each query row:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The row for which function evaluation occurs is called the
            current row.
     </p>
    </li>
    <li class="listitem">
     <p>
      The query rows related to the current row over which
            function evaluation occurs comprise the window for the
            current row.
     </p>
    </li>
   </ul>
  </div>
  <p>
   For example, using the sales information table, these two
        queries perform aggregate operations that produce a single
        global sum for all rows taken as a group, and sums grouped per
        country:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa1571482"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">AS</span> total_profit
       <span class="token keyword">FROM</span> sales<span class="token punctuation">;</span>
<span class="token output"><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><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><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><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> total_profit <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>         7535 <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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><span class="token punctuation">+</span></span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> country<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">AS</span> country_profit
       <span class="token keyword">FROM</span> sales
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> country
       <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> country<span class="token punctuation">;</span>
<span class="token output"><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> country <span class="token punctuation">|</span> country_profit <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Finland <span class="token punctuation">|</span>           1610 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> India   <span class="token punctuation">|</span>           1350 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> USA     <span class="token punctuation">|</span>           4575 <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   By contrast, window operations do not collapse groups of query
        rows to a single output row. Instead, they produce a result for
        each row. Like the preceding queries, the following query uses
   <a class="link" href="aggregate-functions.html#function_sum">
    <code class="literal">
     SUM()
    </code>
   </a>
   , but this time as a window
        function:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa27843023"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span>
         <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> product<span class="token punctuation">,</span> profit<span class="token punctuation">,</span>
         <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">OVER</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> total_profit<span class="token punctuation">,</span>
         <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">OVER</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 keyword">AS</span> country_profit
       <span class="token keyword">FROM</span> sales
       <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> country<span class="token punctuation">,</span> <span class="token datatype">year</span><span class="token punctuation">,</span> product<span class="token punctuation">,</span> profit<span class="token punctuation">;</span>
<span class="token output"><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><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><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><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><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><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><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><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><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><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> year <span class="token punctuation">|</span> country <span class="token punctuation">|</span> product    <span class="token punctuation">|</span> profit <span class="token punctuation">|</span> total_profit <span class="token punctuation">|</span> country_profit <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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><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><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><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><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><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><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><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span>         7535 <span class="token punctuation">|</span>           1610 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Phone      <span class="token punctuation">|</span>    100 <span class="token punctuation">|</span>         7535 <span class="token punctuation">|</span>           1610 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Phone      <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span>         7535 <span class="token punctuation">|</span>           1610 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     75 <span class="token punctuation">|</span>         7535 <span class="token punctuation">|</span>           1350 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     75 <span class="token punctuation">|</span>         7535 <span class="token punctuation">|</span>           1350 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1200 <span class="token punctuation">|</span>         7535 <span class="token punctuation">|</span>           1350 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     75 <span class="token punctuation">|</span>         7535 <span class="token punctuation">|</span>           4575 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span>         7535 <span class="token punctuation">|</span>           4575 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     50 <span class="token punctuation">|</span>         7535 <span class="token punctuation">|</span>           4575 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1200 <span class="token punctuation">|</span>         7535 <span class="token punctuation">|</span>           4575 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span>         7535 <span class="token punctuation">|</span>           4575 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> TV         <span class="token punctuation">|</span>    100 <span class="token punctuation">|</span>         7535 <span class="token punctuation">|</span>           4575 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> TV         <span class="token punctuation">|</span>    150 <span class="token punctuation">|</span>         7535 <span class="token punctuation">|</span>           4575 <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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><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><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><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><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><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><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><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <a class="indexterm" name="idm46045192001056">
  </a>
  <p>
   Each window operation in the query is signified by inclusion of
        an
   <code class="literal">
    OVER
   </code>
   clause that specifies how to
        partition query rows into groups for processing by the window
        function:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The first
      <code class="literal">
       OVER
      </code>
      clause is empty, which
            treats the entire set of query rows as a single partition.
            The window function thus produces a global sum, but does so
            for each row.
     </p>
    </li>
    <li class="listitem">
     <p>
      The second
      <code class="literal">
       OVER
      </code>
      clause partitions rows by
            country, producing a sum per partition (per country). The
            function produces this sum for each partition row.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Window functions are permitted only in the select list and
   <code class="literal">
    ORDER BY
   </code>
   clause. Query result rows are
        determined from the
   <code class="literal">
    FROM
   </code>
   clause, after
   <code class="literal">
    WHERE
   </code>
   ,
   <code class="literal">
    GROUP BY
   </code>
   , and
   <code class="literal">
    HAVING
   </code>
   processing, and windowing execution
        occurs before
   <code class="literal">
    ORDER BY
   </code>
   ,
   <code class="literal">
    LIMIT
   </code>
   , and
   <code class="literal">
    SELECT
        DISTINCT
   </code>
   .
  </p>
  <p>
   The
   <code class="literal">
    OVER
   </code>
   clause is permitted for many
        aggregate functions, which therefore can be used as window or
        nonwindow functions, depending on whether the
   <code class="literal">
    OVER
   </code>
   clause is present or absent:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa99035177"><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 function">AVG</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">BIT_AND</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">BIT_OR</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">BIT_XOR</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">JSON_ARRAYAGG</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">JSON_OBJECTAGG</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">MAX</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">MIN</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">STDDEV_POP</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">STDDEV</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">STD</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">STDDEV_SAMP</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">SUM</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">VAR_POP</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">VARIANCE</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">VAR_SAMP</span><span class="token punctuation">(</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   For details about each aggregate function, see
   <a class="xref" href="aggregate-functions.html" title="14.19.1 Aggregate Function Descriptions">
    Section 14.19.1, “Aggregate Function Descriptions”
   </a>
   .
  </p>
  <p>
   MySQL also supports nonaggregate functions that are used only as
        window functions. For these, the
   <code class="literal">
    OVER
   </code>
   clause
        is mandatory:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa312564"><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 function">CUME_DIST</span><span class="token punctuation">(</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 function">FIRST_VALUE</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">LAG</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">LAST_VALUE</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">LEAD</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">NTH_VALUE</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">NTILE</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token function">PERCENT_RANK</span><span class="token punctuation">(</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 function">ROW_NUMBER</span><span class="token punctuation">(</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   For details about each nonaggregate function, see
   <a class="xref" href="window-function-descriptions.html" title="14.20.1 Window Function Descriptions">
    Section 14.20.1, “Window Function Descriptions”
   </a>
   .
  </p>
  <p>
   As an example of one of those nonaggregate window functions,
        this query uses
   <a class="link" href="window-function-descriptions.html#function_row-number">
    <code class="literal">
     ROW_NUMBER()
    </code>
   </a>
   ,
        which produces the row number of each row within its partition.
        In this case, rows are numbered per country. By default,
        partition rows are unordered and row numbering is
        nondeterministic. To sort partition rows, include an
   <code class="literal">
    ORDER BY
   </code>
   clause within the window definition.
        The query uses unordered and ordered partitions (the
   <code class="literal">
    row_num1
   </code>
   and
   <code class="literal">
    row_num2
   </code>
   columns) to illustrate the difference between omitting and
        including
   <code class="literal">
    ORDER BY
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa96227456"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span>
         <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> product<span class="token punctuation">,</span> profit<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">PARTITION</span> <span class="token keyword">BY</span> country<span class="token punctuation">)</span> <span class="token keyword">AS</span> row_num1<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">PARTITION</span> <span class="token keyword">BY</span> country <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token datatype">year</span><span class="token punctuation">,</span> product<span class="token punctuation">)</span> <span class="token keyword">AS</span> row_num2
       <span class="token keyword">FROM</span> sales<span class="token punctuation">;</span>
<span class="token output"><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><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><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><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><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><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><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><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> year <span class="token punctuation">|</span> country <span class="token punctuation">|</span> product    <span class="token punctuation">|</span> profit <span class="token punctuation">|</span> row_num1 <span class="token punctuation">|</span> row_num2 <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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><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><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><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><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><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Phone      <span class="token punctuation">|</span>    100 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Phone      <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     75 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     75 <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1200 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     75 <span class="token punctuation">|</span>        5 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span>        4 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     50 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span>        4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1200 <span class="token punctuation">|</span>        7 <span class="token punctuation">|</span>        5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> TV         <span class="token punctuation">|</span>    150 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>        6 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> TV         <span class="token punctuation">|</span>    100 <span class="token punctuation">|</span>        6 <span class="token punctuation">|</span>        7 <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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><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><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><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><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><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   As mentioned previously, to use a window function (or treat an
        aggregate function as a window function), include an
   <code class="literal">
    OVER
   </code>
   clause following the function call. The
   <code class="literal">
    OVER
   </code>
   clause has two forms:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71486739"><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">over_clause</em>:
    {<span class="token keyword">OVER</span> <span class="token punctuation">(</span><em class="replaceable">window_spec</em><span class="token punctuation">)</span> <span class="token operator">|</span> <span class="token keyword">OVER</span> <em class="replaceable">window_name</em>}</code></pre>
  </div>
  <p>
   Both forms define how the window function should process query
        rows. They differ in whether the window is defined directly in
        the
   <code class="literal">
    OVER
   </code>
   clause, or supplied by a reference
        to a named window defined elsewhere in the query:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      In the first case, the window specification appears directly
            in the
      <code class="literal">
       OVER
      </code>
      clause, between the
            parentheses.
     </p>
    </li>
    <li class="listitem">
     <p>
      In the second case,
      <em class="replaceable">
       <code>
        window_name
       </code>
      </em>
      is the name for a window specification defined by a
      <code class="literal">
       WINDOW
      </code>
      clause elsewhere in the query. For
            details, see
      <a class="xref" href="window-functions-named-windows.html" title="14.20.4 Named Windows">
       Section 14.20.4, “Named Windows”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   For
   <code class="literal">
    OVER
        (
    <em class="replaceable">
     <code>
      window_spec
     </code>
    </em>
    )
   </code>
   syntax, the
        window specification has several parts, all optional:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa92301187"><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>
   If
   <code class="literal">
    OVER()
   </code>
   is empty, the window consists of
        all query rows and the window function computes a result using
        all rows. Otherwise, the clauses present within the parentheses
        determine which query rows are used to compute the function
        result and how they are partitioned and ordered:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <em class="replaceable">
       <code>
        window_name
       </code>
      </em>
      : The name of a window
            defined by a
      <code class="literal">
       WINDOW
      </code>
      clause elsewhere in
            the query. If
      <em class="replaceable">
       <code>
        window_name
       </code>
      </em>
      appears
            by itself within the
      <code class="literal">
       OVER
      </code>
      clause, it
            completely defines the window. If partitioning, ordering, or
            framing clauses are also given, they modify interpretation
            of the named window. For details, see
      <a class="xref" href="window-functions-named-windows.html" title="14.20.4 Named Windows">
       Section 14.20.4, “Named Windows”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <em class="replaceable">
       <code>
        partition_clause
       </code>
      </em>
      : A
      <code class="literal">
       PARTITION BY
      </code>
      clause indicates how to
            divide the query rows into groups. The window function
            result for a given row is based on the rows of the partition
            that contains the row. If
      <code class="literal">
       PARTITION BY
      </code>
      is
            omitted, there is a single partition consisting of all query
            rows.
     </p>
     <a class="indexterm" name="idm46045191946096">
     </a>
     <a class="indexterm" name="idm46045191944608">
     </a>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Partitioning for window functions differs from table
              partitioning. For information about table partitioning,
              see
       <a class="xref" href="partitioning.html" title="Chapter 26 Partitioning">
        Chapter 26,
        <i>
         Partitioning
        </i>
       </a>
       .
      </p>
     </div>
     <p>
      <em class="replaceable">
       <code>
        partition_clause
       </code>
      </em>
      has this syntax:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa43955496"><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">partition_clause</em>:
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <em class="replaceable">expr</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">expr</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
     </div>
     <p>
      Standard SQL requires
      <code class="literal">
       PARTITION BY
      </code>
      to be
            followed by column names only. A MySQL extension is to
            permit expressions, not just column names. For example, if a
            table contains a
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      column named
      <code class="literal">
       ts
      </code>
      , standard SQL permits
      <code class="literal">
       PARTITION BY ts
      </code>
      but not
      <code class="literal">
       PARTITION BY HOUR(ts)
      </code>
      , whereas MySQL
            permits both.
     </p>
    </li>
    <li class="listitem">
     <p>
      <em class="replaceable">
       <code>
        order_clause
       </code>
      </em>
      : An
      <code class="literal">
       ORDER
            BY
      </code>
      clause indicates how to sort rows in each
            partition. Partition rows that are equal according to the
      <code class="literal">
       ORDER BY
      </code>
      clause are considered peers. If
      <code class="literal">
       ORDER BY
      </code>
      is omitted, partition rows are
            unordered, with no processing order implied, and all
            partition rows are peers.
     </p>
     <a class="indexterm" name="idm46045191930032">
     </a>
     <a class="indexterm" name="idm46045191928544">
     </a>
     <p>
      <em class="replaceable">
       <code>
        order_clause
       </code>
      </em>
      has this syntax:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72213616"><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">order_clause</em>:
    <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">expr</em> <span class="token punctuation">[</span><span class="token keyword">ASC</span><span class="token operator">|</span><span class="token keyword">DESC</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">expr</em> <span class="token punctuation">[</span><span class="token keyword">ASC</span><span class="token operator">|</span><span class="token keyword">DESC</span><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>
      Each
      <code class="literal">
       ORDER BY
      </code>
      expression optionally can
            be followed by
      <code class="literal">
       ASC
      </code>
      or
      <code class="literal">
       DESC
      </code>
      to indicate sort direction. The
            default is
      <code class="literal">
       ASC
      </code>
      if no direction is
            specified.
      <code class="literal">
       NULL
      </code>
      values sort first for
            ascending sorts, last for descending sorts.
     </p>
     <p>
      An
      <code class="literal">
       ORDER BY
      </code>
      in a window definition
            applies within individual partitions. To sort the result set
            as a whole, include an
      <code class="literal">
       ORDER BY
      </code>
      at the
            query top level.
     </p>
    </li>
    <li class="listitem">
     <p>
      <em class="replaceable">
       <code>
        frame_clause
       </code>
      </em>
      : A frame is a subset
            of the current partition and the frame clause specifies how
            to define the subset. The frame clause has many subclauses
            of its own. For details, see
      <a class="xref" href="window-functions-frames.html" title="14.20.3 Window Function Frame Specification">
       Section 14.20.3, “Window Function Frame Specification”
      </a>
      .
     </p>
     <a class="indexterm" name="idm46045191915728">
     </a>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
