<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="window-function-descriptions">
      </a>
      14.20.1 Window Function Descriptions
     </h3>
    </div>
   </div>
  </div>
  <p>
   This section describes nonaggregate window functions that, for
        each row from a query, perform a calculation using rows related
        to that row. Most aggregate functions also can be used as window
        functions; 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>
   For window function usage information and examples, and
        definitions of terms such as the
   <code class="literal">
    OVER
   </code>
   clause,
        window, partition, frame, and peer, see
   <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="table">
   <a name="idm46045192347696">
   </a>
   <p class="title">
    <b>
     Table 14.30 Window Functions
    </b>
   </p>
   <div class="table-contents">
    <table frame="box" rules="all" summary="A reference that lists window functions.">
     <colgroup>
      <col style="width: 28%"/>
      <col style="width: 71%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Name
       </th>
       <th>
        Description
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        <a class="link" href="window-function-descriptions.html#function_cume-dist">
         <code class="literal">
          CUME_DIST()
         </code>
        </a>
       </td>
       <td>
        Cumulative distribution value
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="window-function-descriptions.html#function_dense-rank">
         <code class="literal">
          DENSE_RANK()
         </code>
        </a>
       </td>
       <td>
        Rank of current row within its partition, without gaps
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="window-function-descriptions.html#function_first-value">
         <code class="literal">
          FIRST_VALUE()
         </code>
        </a>
       </td>
       <td>
        Value of argument from first row of window frame
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="window-function-descriptions.html#function_lag">
         <code class="literal">
          LAG()
         </code>
        </a>
       </td>
       <td>
        Value of argument from row lagging current row within partition
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="window-function-descriptions.html#function_last-value">
         <code class="literal">
          LAST_VALUE()
         </code>
        </a>
       </td>
       <td>
        Value of argument from last row of window frame
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="window-function-descriptions.html#function_lead">
         <code class="literal">
          LEAD()
         </code>
        </a>
       </td>
       <td>
        Value of argument from row leading current row within partition
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="window-function-descriptions.html#function_nth-value">
         <code class="literal">
          NTH_VALUE()
         </code>
        </a>
       </td>
       <td>
        Value of argument from N-th row of window frame
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="window-function-descriptions.html#function_ntile">
         <code class="literal">
          NTILE()
         </code>
        </a>
       </td>
       <td>
        Bucket number of current row within its partition.
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="window-function-descriptions.html#function_percent-rank">
         <code class="literal">
          PERCENT_RANK()
         </code>
        </a>
       </td>
       <td>
        Percentage rank value
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="window-function-descriptions.html#function_rank">
         <code class="literal">
          RANK()
         </code>
        </a>
       </td>
       <td>
        Rank of current row within its partition, with gaps
       </td>
      </tr>
      <tr>
       <td>
        <a class="link" href="window-function-descriptions.html#function_row-number">
         <code class="literal">
          ROW_NUMBER()
         </code>
        </a>
       </td>
       <td>
        Number of current row within its partition
       </td>
      </tr>
     </tbody>
    </table>
   </div>
   <div class="table-contents">
    <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 401px; width: 580px;">
     <thead>
      <tr>
       <th style="width: 163.828px;">
        Name
       </th>
       <th style="width: 415.438px;">
        Description
       </th>
      </tr>
     </thead>
    </table>
   </div>
  </div>
  <br class="table-break"/>
  <p>
   In the following function descriptions,
   <em class="replaceable">
    <code>
     over_clause
    </code>
   </em>
   represents the
   <code class="literal">
    OVER
   </code>
   clause, 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>
   . Some window functions
        permit a
   <em class="replaceable">
    <code>
     null_treatment
    </code>
   </em>
   clause that
        specifies how to handle
   <code class="literal">
    NULL
   </code>
   values when
        calculating results. This clause is optional. It is part of the
        SQL standard, but the MySQL implementation permits only
   <code class="literal">
    RESPECT NULLS
   </code>
   (which is also the default).
        This means that
   <code class="literal">
    NULL
   </code>
   values are considered
        when calculating results.
   <code class="literal">
    IGNORE NULLS
   </code>
   is
        parsed, but produces an error.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <a name="function_cume-dist">
     </a>
     <p>
      <a class="link" href="window-function-descriptions.html#function_cume-dist">
       <code class="literal">
        CUME_DIST()
       </code>
      </a>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
     </p>
     <a class="indexterm" name="idm46045192296928">
     </a>
     <p>
      Returns the cumulative distribution of a value within a
            group of values; that is, the percentage of partition values
            less than or equal to the value in the current row. This
            represents the number of rows preceding or peer with the
            current row in the window ordering of the window partition
            divided by the total number of rows in the window partition.
            Return values range from 0 to 1.
     </p>
     <p>
      This function should be used with
      <code class="literal">
       ORDER
            BY
      </code>
      to sort partition rows into the desired order.
            Without
      <code class="literal">
       ORDER BY
      </code>
      , all rows are peers and
            have value
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      /
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      =
            1, where
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      is the partition size.
     </p>
     <p>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
      is 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>
     <p>
      The following query shows, for the set of values in the
      <code class="literal">
       val
      </code>
      column, the
      <code class="literal">
       CUME_DIST()
      </code>
      value for each row, as well
            as the percentage rank value returned by the similar
      <code class="literal">
       PERCENT_RANK()
      </code>
      function. For reference,
            the query also displays row numbers using
      <a class="link" href="window-function-descriptions.html#function_row-number">
       <code class="literal">
        ROW_NUMBER()
       </code>
      </a>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34991824"><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>
         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">CUME_DIST</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">'cume_dist'</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 keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'percent_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>
<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>
<span class="token output"><span class="token punctuation">|</span> val  <span class="token punctuation">|</span> row_number <span class="token punctuation">|</span> cume_dist          <span class="token punctuation">|</span> percent_rank <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>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>          1 <span class="token punctuation">|</span> 0.2222222222222222 <span class="token punctuation">|</span>            0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>          2 <span class="token punctuation">|</span> 0.2222222222222222 <span class="token punctuation">|</span>            0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span>          3 <span class="token punctuation">|</span> 0.3333333333333333 <span class="token punctuation">|</span>         0.25 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>          4 <span class="token punctuation">|</span> 0.6666666666666666 <span class="token punctuation">|</span>        0.375 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>          5 <span class="token punctuation">|</span> 0.6666666666666666 <span class="token punctuation">|</span>        0.375 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>          6 <span class="token punctuation">|</span> 0.6666666666666666 <span class="token punctuation">|</span>        0.375 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    4 <span class="token punctuation">|</span>          7 <span class="token punctuation">|</span> 0.8888888888888888 <span class="token punctuation">|</span>         0.75 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    4 <span class="token punctuation">|</span>          8 <span class="token punctuation">|</span> 0.8888888888888888 <span class="token punctuation">|</span>         0.75 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>          9 <span class="token punctuation">|</span>                  1 <span class="token punctuation">|</span>            1 <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></code></pre>
     </div>
    </li>
    <li class="listitem">
     <a name="function_dense-rank">
     </a>
     <p>
      <a class="link" href="window-function-descriptions.html#function_dense-rank">
       <code class="literal">
        DENSE_RANK()
       </code>
      </a>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
     </p>
     <a class="indexterm" name="idm46045192273952">
     </a>
     <p>
      Returns the rank of the current row within its partition,
            without gaps. Peers are considered ties and receive the same
            rank. This function assigns consecutive ranks to peer
            groups; the result is that groups of size greater than one
            do not produce noncontiguous rank numbers. For an example,
            see the
      <a class="link" href="window-function-descriptions.html#function_rank">
       <code class="literal">
        RANK()
       </code>
      </a>
      function
            description.
     </p>
     <p>
      This function should be used with
      <code class="literal">
       ORDER
            BY
      </code>
      to sort partition rows into the desired order.
            Without
      <code class="literal">
       ORDER BY
      </code>
      , all rows are peers.
     </p>
     <p>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
      is 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>
    </li>
    <li class="listitem">
     <a name="function_first-value">
     </a>
     <p>
      <a class="link" href="window-function-descriptions.html#function_first-value">
       <code class="literal">
        FIRST_VALUE(
        <em class="replaceable">
         <code>
          expr
         </code>
        </em>
        )
       </code>
      </a>
      [
      <em class="replaceable">
       <code>
        null_treatment
       </code>
      </em>
      ]
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
     </p>
     <a class="indexterm" name="idm46045192261104">
     </a>
     <p>
      Returns the value of
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      from
            the first row of the window frame.
     </p>
     <p>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
      is 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>
      .
      <em class="replaceable">
       <code>
        null_treatment
       </code>
      </em>
      is as described in
            the section introduction.
     </p>
     <p>
      The following query demonstrates
      <a class="link" href="window-function-descriptions.html#function_first-value">
       <code class="literal">
        FIRST_VALUE()
       </code>
      </a>
      ,
      <a class="link" href="window-function-descriptions.html#function_last-value">
       <code class="literal">
        LAST_VALUE()
       </code>
      </a>
      , and two
            instances of
      <a class="link" href="window-function-descriptions.html#function_nth-value">
       <code class="literal">
        NTH_VALUE()
       </code>
      </a>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11556963"><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">time</span><span class="token punctuation">,</span> <span class="token keyword">subject</span><span class="token punctuation">,</span> val<span class="token punctuation">,</span>
         <span class="token function">FIRST_VALUE</span><span class="token punctuation">(</span>val<span class="token punctuation">)</span>  <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'first'</span><span class="token punctuation">,</span>
         <span class="token function">LAST_VALUE</span><span class="token punctuation">(</span>val<span class="token punctuation">)</span>   <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'last'</span><span class="token punctuation">,</span>
         <span class="token function">NTH_VALUE</span><span class="token punctuation">(</span>val<span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'second'</span><span class="token punctuation">,</span>
         <span class="token function">NTH_VALUE</span><span class="token punctuation">(</span>val<span class="token punctuation">,</span> <span class="token number">4</span><span class="token punctuation">)</span> <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'fourth'</span>
       <span class="token keyword">FROM</span> observations
       <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> <span class="token keyword">subject</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token datatype">time</span>
                    <span class="token keyword">ROWS</span> <span class="token keyword">UNBOUNDED</span> <span class="token keyword">PRECEDING</span><span class="token punctuation">)</span><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> time     <span class="token punctuation">|</span> subject <span class="token punctuation">|</span> val  <span class="token punctuation">|</span> first <span class="token punctuation">|</span> last <span class="token punctuation">|</span> second <span class="token punctuation">|</span> fourth <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> 07:00:00 <span class="token punctuation">|</span> st113   <span class="token punctuation">|</span>   10 <span class="token punctuation">|</span>    10 <span class="token punctuation">|</span>   10 <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:15:00 <span class="token punctuation">|</span> st113   <span class="token punctuation">|</span>    9 <span class="token punctuation">|</span>    10 <span class="token punctuation">|</span>    9 <span class="token punctuation">|</span>      9 <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:30:00 <span class="token punctuation">|</span> st113   <span class="token punctuation">|</span>   25 <span class="token punctuation">|</span>    10 <span class="token punctuation">|</span>   25 <span class="token punctuation">|</span>      9 <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:45:00 <span class="token punctuation">|</span> st113   <span class="token punctuation">|</span>   20 <span class="token punctuation">|</span>    10 <span class="token punctuation">|</span>   20 <span class="token punctuation">|</span>      9 <span class="token punctuation">|</span>     20 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:00:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span>     0 <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:15:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>   10 <span class="token punctuation">|</span>     0 <span class="token punctuation">|</span>   10 <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:30:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>     0 <span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span>   NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 07:45:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>   30 <span class="token punctuation">|</span>     0 <span class="token punctuation">|</span>   30 <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span>     30 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 08:00:00 <span class="token punctuation">|</span> xh458   <span class="token punctuation">|</span>   25 <span class="token punctuation">|</span>     0 <span class="token punctuation">|</span>   25 <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span>     30 <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>
      Each function uses the rows in the current frame, which, per
            the window definition shown, extends from the first
            partition row to the current row. For the
      <a class="link" href="window-function-descriptions.html#function_nth-value">
       <code class="literal">
        NTH_VALUE()
       </code>
      </a>
      calls, the
            current frame does not always include the requested row; in
            such cases, the return value is
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <a name="function_lag">
     </a>
     <p>
      <a class="link" href="window-function-descriptions.html#function_lag">
       <code class="literal">
        LAG(
        <em class="replaceable">
         <code>
          expr
         </code>
        </em>
        [,
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
        [,
        <em class="replaceable">
         <code>
          default
         </code>
        </em>
        ]])
       </code>
      </a>
      [
      <em class="replaceable">
       <code>
        null_treatment
       </code>
      </em>
      ]
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
     </p>
     <a class="indexterm" name="idm46045192234112">
     </a>
     <p>
      Returns the value of
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      from
            the row that lags (precedes) the current row by
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      rows within its partition. If
            there is no such row, the return value is
      <em class="replaceable">
       <code>
        default
       </code>
      </em>
      . For example, if
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      is 3, the return value is
      <em class="replaceable">
       <code>
        default
       </code>
      </em>
      for the first three rows.
            If
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      or
      <em class="replaceable">
       <code>
        default
       </code>
      </em>
      are missing, the defaults
            are 1 and
      <code class="literal">
       NULL
      </code>
      , respectively.
     </p>
     <p>
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      must be a literal nonnegative
            integer. If
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      is 0,
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      is evaluated for the current
            row.
     </p>
     <p>
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      cannot be
      <code class="literal">
       NULL
      </code>
      , and must be an integer in the range
      <code class="literal">
       0
      </code>
      to
      <code class="literal">
       2
       <sup>
        63
       </sup>
      </code>
      ,
            inclusive, in any of the following forms:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         an unsigned integer constant literal
        </p>
       </li>
       <li class="listitem">
        <p>
         a positional parameter marker (
         <code class="literal">
          ?
         </code>
         )
        </p>
       </li>
       <li class="listitem">
        <p>
         a user-defined variable
        </p>
       </li>
       <li class="listitem">
        <p>
         a local variable in a stored routine
        </p>
       </li>
      </ul>
     </div>
     <p>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
      is 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>
      .
      <em class="replaceable">
       <code>
        null_treatment
       </code>
      </em>
      is as described in
            the section introduction.
     </p>
     <p>
      <a class="link" href="window-function-descriptions.html#function_lag">
       <code class="literal">
        LAG()
       </code>
      </a>
      (and the similar
      <a class="link" href="window-function-descriptions.html#function_lead">
       <code class="literal">
        LEAD()
       </code>
      </a>
      function) are often
            used to compute differences between rows. The following
            query shows a set of time-ordered observations and, for each
            one, the
      <a class="link" href="window-function-descriptions.html#function_lag">
       <code class="literal">
        LAG()
       </code>
      </a>
      and
      <a class="link" href="window-function-descriptions.html#function_lead">
       <code class="literal">
        LEAD()
       </code>
      </a>
      values from the
            adjoining rows, as well as the differences between the
            current and adjoining rows:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30162872"><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>
         t<span class="token punctuation">,</span> val<span class="token punctuation">,</span>
         <span class="token function">LAG</span><span class="token punctuation">(</span>val<span class="token punctuation">)</span>        <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'lag'</span><span class="token punctuation">,</span>
         <span class="token function">LEAD</span><span class="token punctuation">(</span>val<span class="token punctuation">)</span>       <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'lead'</span><span class="token punctuation">,</span>
         val <span class="token operator">-</span> <span class="token function">LAG</span><span class="token punctuation">(</span>val<span class="token punctuation">)</span>  <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'lag diff'</span><span class="token punctuation">,</span>
         val <span class="token operator">-</span> <span class="token function">LEAD</span><span class="token punctuation">(</span>val<span class="token punctuation">)</span> <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'lead diff'</span>
       <span class="token keyword">FROM</span> series
       <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> t<span class="token punctuation">)</span><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>
<span class="token output"><span class="token punctuation">|</span> t        <span class="token punctuation">|</span> val  <span class="token punctuation">|</span> lag  <span class="token punctuation">|</span> lead <span class="token punctuation">|</span> lag diff <span class="token punctuation">|</span> lead diff <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>
<span class="token output"><span class="token punctuation">|</span> 12:00:00 <span class="token punctuation">|</span>  100 <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>  125 <span class="token punctuation">|</span>     NULL <span class="token punctuation">|</span>       <span class="token punctuation">-</span>25 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 13:00:00 <span class="token punctuation">|</span>  125 <span class="token punctuation">|</span>  100 <span class="token punctuation">|</span>  132 <span class="token punctuation">|</span>       25 <span class="token punctuation">|</span>        <span class="token punctuation">-</span>7 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 14:00:00 <span class="token punctuation">|</span>  132 <span class="token punctuation">|</span>  125 <span class="token punctuation">|</span>  145 <span class="token punctuation">|</span>        7 <span class="token punctuation">|</span>       <span class="token punctuation">-</span>13 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 15:00:00 <span class="token punctuation">|</span>  145 <span class="token punctuation">|</span>  132 <span class="token punctuation">|</span>  140 <span class="token punctuation">|</span>       13 <span class="token punctuation">|</span>         5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 16:00:00 <span class="token punctuation">|</span>  140 <span class="token punctuation">|</span>  145 <span class="token punctuation">|</span>  150 <span class="token punctuation">|</span>       <span class="token punctuation">-</span>5 <span class="token punctuation">|</span>       <span class="token punctuation">-</span>10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 17:00:00 <span class="token punctuation">|</span>  150 <span class="token punctuation">|</span>  140 <span class="token punctuation">|</span>  200 <span class="token punctuation">|</span>       10 <span class="token punctuation">|</span>       <span class="token punctuation">-</span>50 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 18:00:00 <span class="token punctuation">|</span>  200 <span class="token punctuation">|</span>  150 <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>       50 <span class="token punctuation">|</span>      NULL <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></code></pre>
     </div>
     <p>
      In the example, the
      <a class="link" href="window-function-descriptions.html#function_lag">
       <code class="literal">
        LAG()
       </code>
      </a>
      and
      <a class="link" href="window-function-descriptions.html#function_lead">
       <code class="literal">
        LEAD()
       </code>
      </a>
      calls use the default
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      and
      <em class="replaceable">
       <code>
        default
       </code>
      </em>
      values of 1 and
      <code class="literal">
       NULL
      </code>
      , respectively.
     </p>
     <p>
      The first row shows what happens when there is no previous
            row for
      <a class="link" href="window-function-descriptions.html#function_lag">
       <code class="literal">
        LAG()
       </code>
      </a>
      : The function
            returns the
      <em class="replaceable">
       <code>
        default
       </code>
      </em>
      value (in
            this case,
      <code class="literal">
       NULL
      </code>
      ). The last row shows the
            same thing when there is no next row for
      <a class="link" href="window-function-descriptions.html#function_lead">
       <code class="literal">
        LEAD()
       </code>
      </a>
      .
     </p>
     <p>
      <a class="link" href="window-function-descriptions.html#function_lag">
       <code class="literal">
        LAG()
       </code>
      </a>
      and
      <a class="link" href="window-function-descriptions.html#function_lead">
       <code class="literal">
        LEAD()
       </code>
      </a>
      also serve to compute
            sums rather than differences. Consider this data set, which
            contains the first few numbers of the Fibonacci series:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa32740462"><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> n <span class="token keyword">FROM</span> fib <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> n<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>
<span class="token output"><span class="token punctuation">|</span> n    <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>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    8 <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></code></pre>
     </div>
     <p>
      The following query shows the
      <a class="link" href="window-function-descriptions.html#function_lag">
       <code class="literal">
        LAG()
       </code>
      </a>
      and
      <a class="link" href="window-function-descriptions.html#function_lead">
       <code class="literal">
        LEAD()
       </code>
      </a>
      values for the rows
            adjacent to the current row. It also uses those functions to
            add to the current row value the values from the preceding
            and following rows. The effect is to generate the next
            number in the Fibonacci series, and the next number after
            that:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa70630976"><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>
         n<span class="token punctuation">,</span>
         <span class="token function">LAG</span><span class="token punctuation">(</span>n<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span>      <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'lag'</span><span class="token punctuation">,</span>
         <span class="token function">LEAD</span><span class="token punctuation">(</span>n<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span>     <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'lead'</span><span class="token punctuation">,</span>
         n <span class="token operator">+</span> <span class="token function">LAG</span><span class="token punctuation">(</span>n<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span>  <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'next_n'</span><span class="token punctuation">,</span>
         n <span class="token operator">+</span> <span class="token function">LEAD</span><span class="token punctuation">(</span>n<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span> <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'next_next_n'</span>
       <span class="token keyword">FROM</span> fib
       <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> n<span class="token punctuation">)</span><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>
<span class="token output"><span class="token punctuation">|</span> n    <span class="token punctuation">|</span> lag  <span class="token punctuation">|</span> lead <span class="token punctuation">|</span> next_n <span class="token punctuation">|</span> next_next_n <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>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span>    1 <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>    1 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>    2 <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>    2 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>      3 <span class="token punctuation">|</span>           5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>    2 <span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>      5 <span class="token punctuation">|</span>           8 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>    8 <span class="token punctuation">|</span>      8 <span class="token punctuation">|</span>          13 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    8 <span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span>     13 <span class="token punctuation">|</span>           8 <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></code></pre>
     </div>
     <p>
      One way to generate the initial set of Fibonacci numbers is
            to use a recursive common table expression. For an example,
            see
      <a class="xref" href="with.html#common-table-expressions-recursive-fibonacci-series" title="Fibonacci Series Generation">
       Fibonacci Series Generation
      </a>
      .
     </p>
     <p>
      You cannot use a negative value for the rows argument of
            this function.
     </p>
    </li>
    <li class="listitem">
     <a name="function_last-value">
     </a>
     <p>
      <a class="link" href="window-function-descriptions.html#function_last-value">
       <code class="literal">
        LAST_VALUE(
        <em class="replaceable">
         <code>
          expr
         </code>
        </em>
        )
       </code>
      </a>
      [
      <em class="replaceable">
       <code>
        null_treatment
       </code>
      </em>
      ]
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
     </p>
     <a class="indexterm" name="idm46045192171312">
     </a>
     <p>
      Returns the value of
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      from
            the last row of the window frame.
     </p>
     <p>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
      is 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>
      .
      <em class="replaceable">
       <code>
        null_treatment
       </code>
      </em>
      is as described in
            the section introduction.
     </p>
     <p>
      For an example, see the
      <a class="link" href="window-function-descriptions.html#function_first-value">
       <code class="literal">
        FIRST_VALUE()
       </code>
      </a>
      function
            description.
     </p>
    </li>
    <li class="listitem">
     <a name="function_lead">
     </a>
     <p>
      <a class="link" href="window-function-descriptions.html#function_lead">
       <code class="literal">
        LEAD(
        <em class="replaceable">
         <code>
          expr
         </code>
        </em>
        [,
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
        [,
        <em class="replaceable">
         <code>
          default
         </code>
        </em>
        ]])
       </code>
      </a>
      [
      <em class="replaceable">
       <code>
        null_treatment
       </code>
      </em>
      ]
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
     </p>
     <a class="indexterm" name="idm46045192158448">
     </a>
     <p>
      Returns the value of
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      from
            the row that leads (follows) the current row by
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      rows within its partition. If
            there is no such row, the return value is
      <em class="replaceable">
       <code>
        default
       </code>
      </em>
      . For example, if
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      is 3, the return value is
      <em class="replaceable">
       <code>
        default
       </code>
      </em>
      for the last three rows.
            If
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      or
      <em class="replaceable">
       <code>
        default
       </code>
      </em>
      are missing, the defaults
            are 1 and
      <code class="literal">
       NULL
      </code>
      , respectively.
     </p>
     <p>
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      must be a literal nonnegative
            integer. If
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      is 0,
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      is evaluated for the current
            row.
     </p>
     <p>
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      cannot be
      <code class="literal">
       NULL
      </code>
      , and must be an integer in the range
      <code class="literal">
       0
      </code>
      to
      <code class="literal">
       2
       <sup>
        63
       </sup>
      </code>
      ,
            inclusive, in any of the following forms:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         an unsigned integer constant literal
        </p>
       </li>
       <li class="listitem">
        <p>
         a positional parameter marker (
         <code class="literal">
          ?
         </code>
         )
        </p>
       </li>
       <li class="listitem">
        <p>
         a user-defined variable
        </p>
       </li>
       <li class="listitem">
        <p>
         a local variable in a stored routine
        </p>
       </li>
      </ul>
     </div>
     <p>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
      is 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>
      .
      <em class="replaceable">
       <code>
        null_treatment
       </code>
      </em>
      is as described in
            the section introduction.
     </p>
     <p>
      For an example, see the
      <a class="link" href="window-function-descriptions.html#function_lag">
       <code class="literal">
        LAG()
       </code>
      </a>
      function description.
     </p>
     <p>
      Use of a negative value for the rows argument of this
            function is not permitted.
     </p>
    </li>
    <li class="listitem">
     <a name="function_nth-value">
     </a>
     <p>
      <a class="link" href="window-function-descriptions.html#function_nth-value">
       <code class="literal">
        NTH_VALUE(
        <em class="replaceable">
         <code>
          expr
         </code>
        </em>
        ,
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
        )
       </code>
      </a>
      [
      <em class="replaceable">
       <code>
        from_first_last
       </code>
      </em>
      ]
            [
      <em class="replaceable">
       <code>
        null_treatment
       </code>
      </em>
      ]
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
     </p>
     <a class="indexterm" name="idm46045192132160">
     </a>
     <p>
      Returns the value of
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      from
            the
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      -th row of the window frame.
            If there is no such row, the return value is
      <code class="literal">
       NULL
      </code>
      .
     </p>
     <p>
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      must be a literal positive
            integer.
     </p>
     <p>
      <em class="replaceable">
       <code>
        from_first_last
       </code>
      </em>
      is part of the
            SQL standard, but the MySQL implementation permits only
      <code class="literal">
       FROM FIRST
      </code>
      (which is also the default).
            This means that calculations begin at the first row of the
            window.
      <code class="literal">
       FROM LAST
      </code>
      is parsed, but produces
            an error. To obtain the same effect as
      <code class="literal">
       FROM
            LAST
      </code>
      (begin calculations at the last row of the
            window), use
      <code class="literal">
       ORDER BY
      </code>
      to sort in reverse
            order.
     </p>
     <p>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
      is 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>
      .
      <em class="replaceable">
       <code>
        null_treatment
       </code>
      </em>
      is as described in
            the section introduction.
     </p>
     <p>
      For an example, see the
      <a class="link" href="window-function-descriptions.html#function_first-value">
       <code class="literal">
        FIRST_VALUE()
       </code>
      </a>
      function
            description.
     </p>
     <p>
      You cannot use
      <code class="literal">
       NULL
      </code>
      for the row argument
            of this function.
     </p>
    </li>
    <li class="listitem">
     <a name="function_ntile">
     </a>
     <p>
      <a class="link" href="window-function-descriptions.html#function_ntile">
       <code class="literal">
        NTILE(
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
        )
       </code>
      </a>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
     </p>
     <a class="indexterm" name="idm46045192113408">
     </a>
     <p>
      Divides a partition into
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      groups
            (buckets), assigns each row in the partition its bucket
            number, and returns the bucket number of the current row
            within its partition. For example, if
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      is 4,
      <code class="literal">
       NTILE()
      </code>
      divides rows into four buckets.
            If
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      is 100,
      <code class="literal">
       NTILE()
      </code>
      divides rows into 100 buckets.
     </p>
     <p>
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      must be a literal positive
            integer. Bucket number return values range from 1 to
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      .
     </p>
     <p>
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      cannot be
      <code class="literal">
       NULL
      </code>
      , and must be an integer in the range
      <code class="literal">
       0
      </code>
      to
      <code class="literal">
       2
       <sup>
        63
       </sup>
      </code>
      ,
            inclusive, in any of the following forms:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         an unsigned integer constant literal
        </p>
       </li>
       <li class="listitem">
        <p>
         a positional parameter marker (
         <code class="literal">
          ?
         </code>
         )
        </p>
       </li>
       <li class="listitem">
        <p>
         a user-defined variable
        </p>
       </li>
       <li class="listitem">
        <p>
         a local variable in a stored routine
        </p>
       </li>
      </ul>
     </div>
     <p>
      This function should be used with
      <code class="literal">
       ORDER
            BY
      </code>
      to sort partition rows into the desired order.
     </p>
     <p>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
      is 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>
     <p>
      The following query shows, for the set of values in the
      <code class="literal">
       val
      </code>
      column, the percentile values
            resulting from dividing the rows into two or four groups.
            For reference, the query also displays row numbers using
      <a class="link" href="window-function-descriptions.html#function_row-number">
       <code class="literal">
        ROW_NUMBER()
       </code>
      </a>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42069335"><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>
         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">NTILE</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span>     <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'ntile2'</span><span class="token punctuation">,</span>
         <span class="token function">NTILE</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span>     <span class="token keyword">OVER</span> w <span class="token keyword">AS</span> <span class="token string">'ntile4'</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>
<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>
<span class="token output"><span class="token punctuation">|</span> val  <span class="token punctuation">|</span> row_number <span class="token punctuation">|</span> ntile2 <span class="token punctuation">|</span> ntile4 <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>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>          1 <span class="token punctuation">|</span>      1 <span class="token punctuation">|</span>      1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>          2 <span class="token punctuation">|</span>      1 <span class="token punctuation">|</span>      1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span>          3 <span class="token punctuation">|</span>      1 <span class="token punctuation">|</span>      1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>          4 <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>    3 <span class="token punctuation">|</span>          5 <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>    3 <span class="token punctuation">|</span>          6 <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>    4 <span class="token punctuation">|</span>          7 <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>    4 <span class="token punctuation">|</span>          8 <span class="token punctuation">|</span>      2 <span class="token punctuation">|</span>      4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>          9 <span class="token punctuation">|</span>      2 <span class="token punctuation">|</span>      4 <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></code></pre>
     </div>
     <p>
      The construct
      <code class="literal">
       NTILE(NULL)
      </code>
      is not
            permitted.
     </p>
    </li>
    <li class="listitem">
     <a name="function_percent-rank">
     </a>
     <p>
      <a class="link" href="window-function-descriptions.html#function_percent-rank">
       <code class="literal">
        PERCENT_RANK()
       </code>
      </a>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
     </p>
     <a class="indexterm" name="idm46045192080720">
     </a>
     <p>
      Returns the percentage of partition values less than the
            value in the current row, excluding the highest value.
            Return values range from 0 to 1 and represent the row
            relative rank, calculated as the result of this formula,
            where
      <em class="replaceable">
       <code>
        rank
       </code>
      </em>
      is the row rank and
      <em class="replaceable">
       <code>
        rows
       </code>
      </em>
      is the number of partition
            rows:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-clike"><div class="docs-select-all right" id="sa89285317"><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-clike"><span class="token punctuation">(</span><em class="replaceable">rank</em> <span class="token operator">-</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token operator">/</span> <span class="token punctuation">(</span><em class="replaceable">rows</em> <span class="token operator">-</span> <span class="token number">1</span><span class="token punctuation">)</span></code></pre>
     </div>
     <p>
      This function should be used with
      <code class="literal">
       ORDER
            BY
      </code>
      to sort partition rows into the desired order.
            Without
      <code class="literal">
       ORDER BY
      </code>
      , all rows are peers.
     </p>
     <p>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
      is 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>
     <p>
      For an example, see the
      <a class="link" href="window-function-descriptions.html#function_cume-dist">
       <code class="literal">
        CUME_DIST()
       </code>
      </a>
      function
            description.
     </p>
    </li>
    <li class="listitem">
     <a name="function_rank">
     </a>
     <p>
      <a class="link" href="window-function-descriptions.html#function_rank">
       <code class="literal">
        RANK()
       </code>
      </a>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
     </p>
     <a class="indexterm" name="idm46045192065616">
     </a>
     <p>
      Returns the rank of the current row within its partition,
            with gaps. Peers are considered ties and receive the same
            rank. This function does not assign consecutive ranks to
            peer groups if groups of size greater than one exist; the
            result is noncontiguous rank numbers.
     </p>
     <p>
      This function should be used with
      <code class="literal">
       ORDER
            BY
      </code>
      to sort partition rows into the desired order.
            Without
      <code class="literal">
       ORDER BY
      </code>
      , all rows are peers.
     </p>
     <p>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
      is 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>
     <p>
      The following query shows the difference between
      <a class="link" href="window-function-descriptions.html#function_rank">
       <code class="literal">
        RANK()
       </code>
      </a>
      , which produces ranks
            with gaps, and
      <a class="link" href="window-function-descriptions.html#function_dense-rank">
       <code class="literal">
        DENSE_RANK()
       </code>
      </a>
      ,
            which produces ranks without gaps. The query shows rank
            values for each member of a set of values in the
      <code class="literal">
       val
      </code>
      column, which contains some
            duplicates.
      <a class="link" href="window-function-descriptions.html#function_rank">
       <code class="literal">
        RANK()
       </code>
      </a>
      assigns
            peers (the duplicates) the same rank value, and the next
            greater value has a rank higher by the number of peers minus
            one.
      <a class="link" href="window-function-descriptions.html#function_dense-rank">
       <code class="literal">
        DENSE_RANK()
       </code>
      </a>
      also
            assigns peers the same rank value, but the next higher value
            has a rank one greater. For reference, the query also
            displays row numbers using
      <a class="link" href="window-function-descriptions.html#function_row-number">
       <code class="literal">
        ROW_NUMBER()
       </code>
      </a>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28340814"><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>
         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>
<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>
<span class="token output"><span class="token punctuation">|</span> val  <span class="token punctuation">|</span> row_number <span class="token punctuation">|</span> rank <span class="token punctuation">|</span> dense_rank <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>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>          1 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>          1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>          2 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>          1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span>          3 <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>    3 <span class="token punctuation">|</span>          4 <span class="token punctuation">|</span>    4 <span class="token punctuation">|</span>          3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>          5 <span class="token punctuation">|</span>    4 <span class="token punctuation">|</span>          3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>          6 <span class="token punctuation">|</span>    4 <span class="token punctuation">|</span>          3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    4 <span class="token punctuation">|</span>          7 <span class="token punctuation">|</span>    7 <span class="token punctuation">|</span>          4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    4 <span class="token punctuation">|</span>          8 <span class="token punctuation">|</span>    7 <span class="token punctuation">|</span>          4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>          9 <span class="token punctuation">|</span>    9 <span class="token punctuation">|</span>          5 <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></code></pre>
     </div>
    </li>
    <li class="listitem">
     <a name="function_row-number">
     </a>
     <p>
      <a class="link" href="window-function-descriptions.html#function_row-number">
       <code class="literal">
        ROW_NUMBER()
       </code>
      </a>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
     </p>
     <a class="indexterm" name="idm46045192039680">
     </a>
     <p>
      Returns the number of the current row within its partition.
            Rows numbers range from 1 to the number of partition rows.
     </p>
     <p>
      <code class="literal">
       ORDER BY
      </code>
      affects the order in which rows
            are numbered. Without
      <code class="literal">
       ORDER BY
      </code>
      , row
            numbering is nondeterministic.
     </p>
     <p>
      <a class="link" href="window-function-descriptions.html#function_row-number">
       <code class="literal">
        ROW_NUMBER()
       </code>
      </a>
      assigns peers
            different row numbers. To assign peers the same value, use
      <a class="link" href="window-function-descriptions.html#function_rank">
       <code class="literal">
        RANK()
       </code>
      </a>
      or
      <a class="link" href="window-function-descriptions.html#function_dense-rank">
       <code class="literal">
        DENSE_RANK()
       </code>
      </a>
      . For an example,
            see the
      <a class="link" href="window-function-descriptions.html#function_rank">
       <code class="literal">
        RANK()
       </code>
      </a>
      function
            description.
     </p>
     <p>
      <em class="replaceable">
       <code>
        over_clause
       </code>
      </em>
      is 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>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
