title: Oracle SQL: MAX function in WHERE clause
body: <p>I've always received very helpful tips from this site and I'm very grateful for all of those so once again I'll try my luck!</p>
  <p>I'm trying to use max function in where clause in order to fetch the latest revision of change order.
  So in table I have fields order_no, line_no, chg_order_no (revision number) and amount. So whenever user modifies purchase order then the system creates new change order and order_no and line_no will remain same but chg_order_no and amount changes. Note that chg_order_no can be anything from 1 to e.g. 100</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>order_no</th>
  <th>line_no</th>
  <th>chg_order_no</th>
  <th>amount</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>order1</td>
  <td>1</td>
  <td>1</td>
  <td>100</td>
  </tr>
  <tr>
  <td>order2</td>
  <td>1</td>
  <td>1</td>
  <td>250</td>
  </tr>
  <tr>
  <td>order1</td>
  <td>1</td>
  <td>2</td>
  <td>300</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Now I only want to fetch following rows:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>order_no</th>
  <th>line_no</th>
  <th>chg_order_no</th>
  <th>amount</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>order2</td>
  <td>1</td>
  <td>1</td>
  <td>250</td>
  </tr>
  <tr>
  <td>order1</td>
  <td>1</td>
  <td>2</td>
  <td>300</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I have tried to use following select query but it doesn't seem to work for all orders</p>
  <pre><code>SELECT co.order_no, co.chg_order_no,co.line_no, co.amount 
  FROM CHANGE_ORDER co 
  WHERE co.chg_order_no = (select MAX(chg_order_no) from CHANGE_ORDER)
  </code></pre>
  <p>Thanks in advance</p>