title: ORACLE SQL Query to find min and max value in a group
body: <p>I have the following data and need quick help for the following.(giving EMP table example, as I cannot give real data due to compliance issues) :</p>
  
  <pre><code>SELECT EMPNO,
         JOB,
         HIREDATE,
         HIREDATE AS STARTDATE,
         LEAD(HIREDATE) OVER (ORDER BY HIREDATE) AS ENDDATE
    FROM SCOTT.EMP
   ORDER BY HIREDATE;
  
  EMPNO   JOB        HIREDATE   STARTDATE  ENDDATE  
  --------------------------------------------------
  7369    CLERK      17-Dec-80  17-Dec-80  20-Feb-81  
  7499    SALESMAN   20-Feb-81  20-Feb-81  22-Feb-81  
  7521    SALESMAN   22-Feb-81  22-Feb-81  2-Apr-81  
  7566    MANAGER    2-Apr-81   2-Apr-81   1-May-81  
  7698    MANAGER    1-May-81   1-May-81   9-Jun-81  
  7782    MANAGER    9-Jun-81   9-Jun-81   8-Sep-81  
  7844    SALESMAN   8-Sep-81   8-Sep-81   28-Sep-81  
  7654    SALESMAN   28-Sep-81  28-Sep-81  17-Nov-81  
  7839    PRESIDENT  17-Nov-81  17-Nov-81  3-Dec-81  
  7900    CLERK      3-Dec-81   3-Dec-81   3-Dec-81  
  7902    ANALYST    3-Dec-81   3-Dec-81   23-Jan-82  
  7934    CLERK      23-Jan-82  23-Jan-82  19-Apr-87  
  7788    ANALYST    19-Apr-87  19-Apr-87  23-May-87  
  7876    CLERK      23-May-87  23-May-87  NULL   
  </code></pre>
  
  <p>Here I want the partition by job in oracle SQL, such that the consecutive rows get the same derived start date(DERSTARTDATE) and derived end date(DERENDDATE) as shown below. Can you please advise with SQL in Oracle.</p>
  
  <p>I want something like this:</p>
  
  <pre><code>EMPNO   JOB       HIREDATE   DERSTARTDATE  DERENDDATE   
  -----------------------------------------------------
  7369    CLERK     17-Dec-80  17-Dec-80     20-Feb-81   
  7499    SALESMAN  20-Feb-81  20-Feb-81     2-Apr-81    
  7521    SALESMAN  22-Feb-81  20-Feb-81     2-Apr-81       
  7566    MANAGER   2-Apr-81   2-Apr-81      8-Sep-81       
  7698    MANAGER   1-May-81   2-Apr-81      8-Sep-81    
  7782    MANAGER   9-Jun-81   2-Apr-81      8-Sep-81    
  7844    SALESMAN  8-Sep-81   8-Sep-81      17-Nov-81   
  7654    SALESMAN  28-Sep-81  8-Sep-81      17-Nov-81   
  7839    PRESIDENT 17-Nov-81  17-Nov-81     3-Dec-81    
  7900    CLERK     3-Dec-81   3-Dec-81      3-Dec-81    
  7902    ANALYST   3-Dec-81   3-Dec-81      23-Jan-82   
  7934    CLERK     23-Jan-82  23-Jan-82     19-Apr-87   
  7788    ANALYST   19-Apr-87  19-Apr-87     23-May-87   
  7876    CLERK     23-May-87  23-May-87     NULL    
  </code></pre>
  
  <p>My requirement is if the same job is repeated in consecutive  e.g. for Salesman then the startdate and enddate should be min of startdate and max of enddate in all consecutive columns.</p>
  
  <p>Example:</p>
  
  <ul>
  <li>SALESMAN for both consecutive employees empno= 7499 and empno=7521 should have DERSTARTDATE = 20-Feb-81 and DERENDDATE =2-Apr-81</li>
  <li>SALESMAN for empno= 7844 and empno=7654 should have DERSTARTDATE = 8-Sep-81  and DERENDDATE = 17-Nov-81</li>
  </ul>