SELECTing user with earliest time and joining with other data

Question

I have an Oracle database where I'm trying to select a user field from the earliest row (based on a time field) where certain conditions are met, and I don't know how to do it. Here's the gist of my query:

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'

My problem is I don't know how to SELECT user and get only the value from the row with the earliest time value matching the WHERE conditions. I don't think I can use HAVING since I'm not doing any aggregate functions in my SELECT. The 'earliest time value' condition needs to apply only to the selection of the user field, i.e. I want id and value to be selected for all values of the time field.

I was thinking I could keep the SELECT statement I have above and then JOIN with another SELECT statement that gets the particular user I want.

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
     )

However, this only gets one value for user overall, and there should be a separate user value for each id SELECTed in my first query. How do I limit my SELECT user query by the other query's id field? Is this even the right approach to get what I want?

Answer 1

SELECT  id,
        CASE WHEN value = 'xyz'
             THEN 'Pending'
             ELSE 'Not Pending'
        END AS status,
        time,
        FIRST_VALUE(user) OVER (PARTITION BY id ORDER BY time) AS first_user
FROM    table1
INNER JOIN
        ...
WHERE   subject IN (...) AND
        field = 'Status'