title: Getting values relating to the max and min rows in Oracle
body: <p>In Oracle 11g we need to be able to query a table to pull out information from rows with the highest and lowest values in a certain group. For example using the EMP table we'd like to find the name of person with the highest salary and the name of the person with the lowest salary in each department</p>
  
  <pre><code>DEPTNO   MAX_SAL    MAX_EARNER    MIN_SAL    MIN_EARNER
  -------------------------------------------------------
  10       5000       KING          1300       MILLER
  20       3000       FORD          2975       JONES
  etc
  </code></pre>
  
  <p>(if there are two or more staff with the highest or lowest salaray we want to always return the first in alphabetical order).</p>
  
  <p>A <a href="https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column">Previous Post</a> discussed how to get a value for just the maximum but not both max and min.</p>
  
  <p>We have an untidy solution at the moment based on the link above then applying subsiquent queries but performance is important to us. I predict a good solution will also require analytic functions and possibly a pivot to coalesce the multiple rows into single rows.</p>
  
  <p>Any help greatly appreciated!
  Richard</p>