PostgreSQL - Select Last Row For A Grouping

I have a system in which users can submit holiday requests, which the HR administrators can take action against, such as by approving or rejecting the request. Likewise, the user could cancel the request if they change their mind. I wish to keep everything immutable and fully logged. Thus, I have two tables, one for the original request, and one for a log of all the actions taken against it. The status of the request can be thought of as the last action that was taken against it, and a holiday request has no actions, then it is in the "pending" state.

In this example I might have a table of the actions taken against the request like so:

id    request_id    created_at      action      notes
1     a             2014-02-01      reject     ""
2     b             2014-02-02      approve    ""
3     c             2014-02-03      reject     ""
4     b             2014-02-04      cancel     "Cancelling request because..."
5     d             2014-02-05      reject     "Rejecting because lack of resources."
6     c             2014-02-06      approve    "Overriding rejection as..."

... and I somehow want to transform that into just having the last action for each request like so:

id    request_id    created_at      action      notes
1     a             2014-02-01      reject     ""
4     b             2014-02-04      cancel     "Cancelling request because..."
5     d             2014-02-05      reject     "Rejecting because lack of resources."
6     c             2014-02-06      approve    "Overriding rejection as..."

Luckily, this can easily be achieved by performing the following SQL query:

SELECT  * 
FROM holiday_request_actions
WHERE (request_id, created_at) in (
    SELECT request_id, max(created_at) 
    FROM holiday_request_actions 
    GROUP BY request_id
);

However, it would be possible for two actions to be taken on the same date, so it would be advisable to change the created_at field to be a timestamp (unix or otherwise), and for even further safety, one could set a combined unique key on the request_id, created_at combination.

An example set of SQL statements to create the relevant structures could be:

CREATE TYPE holiday_request_action AS ENUM ('approve', 'reject', 'cancel');

CREATE TABLE holiday_request_actions (
    id uuid NOT NULL PRIMARY KEY,
    request_id uuid NOT NULL,
    created_at int NOT NULL,
    action holiday_request_action
    UNIQUE (request_id, created_at)
);

CREATE TABLE holiday_requests (
    id uuid NOT NULL PRIMARY KEY,
    requestor_id uuid NOT NULL,
    start_date date NOT NULL,
    end_date date NOT NULL,
    reason TEXT NOT NULL
);

Fetching Approved Holiday Requests

Now that we have a query that will return only the last action taken against each holiday request, one can take this a step further by putting it in a nested query, in order to get all of the holiday requests that were approved:

SELECT * FROM holiday_requests 
WHERE id IN (
    SELECT * FROM holiday_request_actions
    WHERE (request_id, created_at) in (
        SELECT request_id, max(created_at) 
        FROM holiday_request_actions 
        GROUP BY request_id
    )
    AND action='approve'
);

References

• Stack Overflow - Postgresql extract last row for each id