title: SELECTing user with earliest time and joining with other data
body: <p>I have an Oracle database where I'm trying to select a <code>user</code> field from the earliest row (based on a <code>time</code> field) where certain conditions are met, and I don't know how to do it.  Here's the gist of my query:</p>
  
  <pre><code>SELECT id,
         CASE WHEN value = 'xyz'
              THEN 'Pending'
              ELSE 'Not Pending'
         END AS status,
         time
  FROM table1
  INNER JOIN ...
  WHERE subject IN (...) AND
        field = 'Status'
  </code></pre>
  
  <p>My problem is I don't know how to SELECT <code>user</code> and get only the value from the row with the earliest <code>time</code> value matching the WHERE conditions.  I don't think I can use <code>HAVING</code> since I'm not doing any aggregate functions in my SELECT.  The 'earliest <code>time</code> value' condition needs to apply <em>only</em> to the selection of the <code>user</code> field, i.e. I want <code>id</code> and <code>value</code> to be selected for <em>all</em> values of the <code>time</code> field.</p>
  
  <p>I was thinking I could keep the SELECT statement I have above and then JOIN with another SELECT statement that gets the particular <code>user</code> I want.</p>
  
  <pre><code>SELECT id, status, time, user
  FROM (
         ...query above...
       ),
       (
         SELECT user
         FROM table1
         WHERE subject in (...) AND
               field = 'Status' AND
               ROWNUM = 1
         ORDER BY time ASC
       )
  </code></pre>
  
  <p>However, this only gets one value for <code>user</code> overall, and there should be a separate <code>user</code> value for each <code>id</code> SELECTed in my first query.  How do I limit my <code>SELECT user</code> query by the other query's <code>id</code> field?  Is this even the right approach to get what I want?</p>