<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="example-maximum-column-group-row">
      </a>
      5.6.4 The Rows Holding the Group-wise Maximum of a Certain Column
     </h3>
    </div>
   </div>
  </div>
  <p>
   <span class="emphasis">
    <em>
     Task: For each article, find the dealer or dealers
        with the most expensive price.
    </em>
   </span>
  </p>
  <p>
   This problem can be solved with a subquery like this one:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3774861"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> article<span class="token punctuation">,</span> dealer<span class="token punctuation">,</span> price
<span class="token keyword">FROM</span>   shop s1
<span class="token keyword">WHERE</span>  price<span class="token operator">=</span><span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>s2<span class="token punctuation">.</span>price<span class="token punctuation">)</span>
              <span class="token keyword">FROM</span> shop s2
              <span class="token keyword">WHERE</span> s1<span class="token punctuation">.</span>article <span class="token operator">=</span> s2<span class="token punctuation">.</span>article<span class="token punctuation">)</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> article<span class="token punctuation">;</span>

<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> article <span class="token punctuation">|</span> dealer <span class="token punctuation">|</span> price <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    0001 <span class="token punctuation">|</span> B      <span class="token punctuation">|</span>  3.99 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    0002 <span class="token punctuation">|</span> A      <span class="token punctuation">|</span> 10.99 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    0003 <span class="token punctuation">|</span> C      <span class="token punctuation">|</span>  1.69 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    0004 <span class="token punctuation">|</span> D      <span class="token punctuation">|</span> 19.95 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   The preceding example uses a correlated subquery, which can be
        inefficient (see
   <a class="xref" href="correlated-subqueries.html" title="15.2.15.7 Correlated Subqueries">
    Section 15.2.15.7, “Correlated Subqueries”
   </a>
   ). Other
        possibilities for solving the problem are to use an uncorrelated
        subquery in the
   <code class="literal">
    FROM
   </code>
   clause, a
   <code class="literal">
    LEFT
        JOIN
   </code>
   , or a common table expression with a window
        function.
  </p>
  <p>
   Uncorrelated subquery:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa1136785"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> s1<span class="token punctuation">.</span>article<span class="token punctuation">,</span> dealer<span class="token punctuation">,</span> s1<span class="token punctuation">.</span>price
<span class="token keyword">FROM</span> shop s1
<span class="token keyword">JOIN</span> <span class="token punctuation">(</span>
  <span class="token keyword">SELECT</span> article<span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>price<span class="token punctuation">)</span> <span class="token keyword">AS</span> price
  <span class="token keyword">FROM</span> shop
  <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> article<span class="token punctuation">)</span> <span class="token keyword">AS</span> s2
  <span class="token keyword">ON</span> s1<span class="token punctuation">.</span>article <span class="token operator">=</span> s2<span class="token punctuation">.</span>article <span class="token operator">AND</span> s1<span class="token punctuation">.</span>price <span class="token operator">=</span> s2<span class="token punctuation">.</span>price
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> article<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <code class="literal">
    LEFT JOIN
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa20573262"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> s1<span class="token punctuation">.</span>article<span class="token punctuation">,</span> s1<span class="token punctuation">.</span>dealer<span class="token punctuation">,</span> s1<span class="token punctuation">.</span>price
<span class="token keyword">FROM</span> shop s1
<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> shop s2 <span class="token keyword">ON</span> s1<span class="token punctuation">.</span>article <span class="token operator">=</span> s2<span class="token punctuation">.</span>article <span class="token operator">AND</span> s1<span class="token punctuation">.</span>price <span class="token operator">&lt;</span> s2<span class="token punctuation">.</span>price
<span class="token keyword">WHERE</span> s2<span class="token punctuation">.</span>article <span class="token operator">IS</span> <span class="token boolean">NULL</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> s1<span class="token punctuation">.</span>article<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    LEFT JOIN
   </code>
   works on the basis that when
   <code class="literal">
    s1.price
   </code>
   is at its maximum value, there is no
   <code class="literal">
    s2.price
   </code>
   with a greater value and thus the
        corresponding
   <code class="literal">
    s2.article
   </code>
   value is
   <code class="literal">
    NULL
   </code>
   . See
   <a class="xref" href="join.html" title="15.2.13.2 JOIN Clause">
    Section 15.2.13.2, “JOIN Clause”
   </a>
   .
  </p>
  <p>
   Common table expression with window function:
  </p>
  <a class="indexterm" name="idm46045324692848">
  </a>
  <a class="indexterm" name="idm46045324691760">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa24252225"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">WITH</span> s1 <span class="token keyword">AS</span> <span class="token punctuation">(</span>
   <span class="token keyword">SELECT</span> article<span class="token punctuation">,</span> dealer<span class="token punctuation">,</span> price<span class="token punctuation">,</span>
          <span class="token function">RANK</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">OVER</span> <span class="token punctuation">(</span><span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> article
                           <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> price <span class="token keyword">DESC</span>
                      <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token punctuation">`</span>Rank<span class="token punctuation">`</span>
     <span class="token keyword">FROM</span> shop
<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> article<span class="token punctuation">,</span> dealer<span class="token punctuation">,</span> price
  <span class="token keyword">FROM</span> s1
  <span class="token keyword">WHERE</span> <span class="token punctuation">`</span>Rank<span class="token punctuation">`</span> <span class="token operator">=</span> <span class="token number">1</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> article<span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
