title: Oracle - select rows with minimal value in a subset
body: <p>I have a following table of dates:</p>
  
  <pre><code>dateID INT (PK),
  personID INT (FK),
  date DATE,
  starttime VARCHAR, --Always in a format of 'HH:MM'
  </code></pre>
  
  <p>What I want to do is I want to pull rows (all columns, including PK) with lowest date (primary condition) and starttime (secondary condition) for every person. For example, if we have</p>
  
  <p>row1(date = '2013-04-01' and starttime = '14:00') </p>
  
  <p>and </p>
  
  <p>row2(date = '2013-04-02' and starttime = '08:00') </p>
  
  <p>row1 will be retrieved, along with all other columns. </p>
  
  <p>So far I have come up with gradual filtering the table, but it`s quite a mess. Is there more efficient way of doing this?</p>
  
  <p>Here is what I made so far:</p>
  
  <pre><code>    SELECT 
      D.id
      , D.personid
      , D.date        
      , D.starttime 
  FROM table D 
  JOIN (
          SELECT --Select lowest time from the subset of lowest dates
              A.personid, 
              B.startdate, 
              MIN(A.starttime) AS starttime 
          FROM table A 
          JOIN (
                  SELECT --Select lowest date for every person to exclude them from outer table
                      personid
                      , MIN(date) AS startdate
                  FROM table
                  GROUP BY personid
              ) B
          ON A.personid = B.peronid
          AND A.date = B.startdate
          GROUP BY 
              A.personid, 
              B.startdate
      ) C
  ON C.personid = D.personid
  AND C.startdate = D.date 
  AND C.starttime = D.starttime
  </code></pre>
  
  <p>It works, but I think there is a more clean/efficient way to do this. Any ideas?</p>
  
  <p>EDIT: Let me expand a question - I also need to extract maximum date (only date, without time) for each person.</p>
  
  <p>The result should look like this:</p>
  
  <pre><code>id
  personid
  max(date) for each person
  min(date) for each person
  min(starttime) for min(date) for each person
  </code></pre>
  
  <p>It is a part of a much larger query (the resulting table is joined with it), and the resulting table must be lightweight enough so that the query won`t execute for too long. With single join with this table (just using min, max for each field I wanted) the query took about 3 seconds, and I would like the resulting query not to take longer than 2-3 times that.</p>