title: MAX not working as expected in Oracle
body: <p>I have an SQL query </p>
  
  <pre><code>SELECT spt.paymenttype,
    MAX(nest.paytypetotal) total
  FROM sportpaymenttype spt
  INNER JOIN (SELECT spt.paymenttype,
                SUM(sod.detailunitprice * sod.detailquantity) paytypetotal
              FROM sportorderdetail sod
              INNER JOIN sportorder so ON so.orderid = sod.orderid
              INNER JOIN sportpaymenttype spt ON spt.paymenttype = so.paymenttype
              GROUP BY spt.paymenttype) nest ON nest.paymenttype = spt.paymenttype
  GROUP BY spt.paymenttype;
  </code></pre>
  
  <p>I expect it to return one row (because of the MAX function) however, it returns 4 rows.  I came up with a painful way to do it properly but I'm wondering, why the max function is behaving this way?</p>
  
  <p>Also, these are the results, where I only expect the first one</p>
  
  <pre><code>PAYMENTTYPE   TOTAL
  Loan          8640.95
  Check         147.34
  Credit Card   479.93
  Cash          25.95
  </code></pre>
  
  <p>What I was wondering is if there was a better way to do this...</p>
  
  <pre><code>SELECT spt.paymenttype,
    nest.paytypetotal total
  FROM sportpaymenttype spt
  INNER JOIN (SELECT spt.paymenttype,
                SUM(sod.detailunitprice * sod.detailquantity) paytypetotal
              FROM sportorderdetail sod
              INNER JOIN sportorder so ON so.orderid = sod.orderid
              INNER JOIN sportpaymenttype spt ON spt.paymenttype = so.paymenttype
              GROUP BY spt.paymenttype) nest ON nest.paymenttype = spt.paymenttype
  WHERE nest.paytypetotal = (SELECT MAX(nest.paytypetotal)
                             FROM (SELECT spt.paymenttype,
                               SUM(sod.detailunitprice * sod.detailquantity) paytypetotal
                             FROM sportorderdetail sod
                             INNER JOIN sportorder so ON so.orderid = sod.orderid
                             INNER JOIN sportpaymenttype spt ON spt.paymenttype = so.paymenttype
                             GROUP BY spt.paymenttype) nest);
  </code></pre>
  
  <p>Thanks.</p>