PostgreSQL select master records and display details columns for specific record

Question

First I must apologize for bad topic text, but I don't have any better idea. Maybe because of that, I didn't find solution when searching the web.

I have 2 tables: master and details who of course has foreign key to master. I would like to get all rows and all fields from master and fields from details for specific record (let's say order of some column) for every row in master.

I tried like this:

SELECT master.id, master.title, temp2.master_id, temp2.datetime, temp2.title_details
FROM master
LEFT JOIN (SELECT master_id, datetime, title AS title_details FROM details ORDER BY datetime DESC) temp2 ON temp2.master_id=master.id

//and this:
SELECT master.id, master.title, 
(SELECT master_id, datetime, title AS title_details FROM details WHERE master.id=details.master_id ORDER BY datetime DESC) 
FROM master
//but of course: subquery must return only one column

But this is not working.

Example what I want to do:

Master:
id  title
1   test
2   blab
3   something

Details:
id  master_id   datetime    title
1   1           2004-...    t: 1.1
2   1           2005-...    t: 2.1
3   1           2006-...    t: 3.1
4   2           2004-...    t: 4.2
5   2           2005-...    t: 5.2
6   3           2006-...    t: 6.3  

Expected output:
id  title       datetime    title_details
1   test        2006-...        t: 3.1
2   blab        2005-...        t: 5.2
3   something   2006-...        t: 6.3

Because it is hard for me to explain what I need, here is the PHP code (from head) what I don't want to do:

$q = Database::$DB->prepare("SELECT * FROM master");
$q2 = Database::$DB->prepare("SELECT * FROM details WHERE master_id=? ORDER BY datetime DESC LIMIT 1");
$rows = $q->execute();
foreach ($rows as $row)
{
    $q2->execute($row->id);
    $row->AdditionalFields = $q2->fetch();
} 

In other words, I don't want to iterate through all master rows and select data for specific ONE record (last - ORDER BY datetime) in details.

I tried all different UNIONs, JOINS and SUBQUERIES, but without success.

EDITED (comment on different answers):

The actual queries are:

SELECT DISTINCT ON (todo_topics.id) todo_topics.id, todo_topics.user_id, users.username AS author, todo_topics.title, todo_topics.datetime_created, todo_topics.version, todo_topics.todo_status_id, todo_statuses.icon_image, 
    todo_topics.version_status_changed, todo_posts.text, u.username AS last_poster, todo_posts.user_id as last_poster_id
FROM todo_topics
LEFT JOIN todo_statuses ON todo_statuses.id = todo_topics.todo_status_id
LEFT JOIN users ON users.id = todo_topics.user_id
LEFT JOIN todo_posts ON todo_topics.id=todo_posts.todo_topic_id
LEFT JOIN users u ON u.id = todo_posts.user_id
ORDER BY todo_topics.id, todo_posts.datetime_created  DESC

"Total runtime: 0.863 ms"

    SELECT
      todo_topics.id, todo_topics.user_id, users.username AS author, todo_topics.title, todo_topics.datetime_created, todo_topics.version, todo_topics.todo_status_id, todo_statuses.icon_image, 
        todo_topics.version_status_changed, todo_posts.text, u.username AS last_poster, todo_posts.user_id as last_poster_id
    FROM
      todo_topics
      LEFT JOIN todo_statuses ON todo_statuses.id = todo_topics.todo_status_id
    LEFT JOIN users ON users.id = todo_topics.user_id

    INNER JOIN
    (
      SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY todo_topic_id ORDER BY datetime_created DESC) AS ordinal
      FROM
        todo_posts
    )
      AS todo_posts
        ON todo_posts.todo_topic_id = todo_topics.id
    LEFT JOIN users u ON u.id = todo_posts.user_id
    WHERE
      todo_posts.ordinal = 1

"Total runtime: 1.281 ms"

 SELECT
  todo_topics.id, todo_topics.user_id, users.username AS author, todo_topics.title, todo_topics.datetime_created, todo_topics.version, todo_topics.todo_status_id, todo_statuses.icon_image, 
    todo_topics.version_status_changed, todo_posts.text, u.username AS last_poster, todo_posts.user_id as last_poster_id
FROM
  todo_topics
LEFT JOIN todo_statuses ON todo_statuses.id = todo_topics.todo_status_id
LEFT JOIN users ON users.id = todo_topics.user_id
INNER JOIN
(
  SELECT
    todo_topic_id,
    MAX(datetime_created)  AS max_datetime
  FROM
    todo_posts
  GROUP BY
    todo_topic_id
)
  AS details_lookup
    ON  details_lookup.todo_topic_id = todo_topics.id
INNER JOIN
  todo_posts
    ON  todo_posts.todo_topic_id = details_lookup.todo_topic_id
    AND todo_posts.datetime_created  = details_lookup.max_datetime
LEFT JOIN users u ON u.id = todo_posts.user_id

"Total runtime: 1.143 ms"

If someone want to know what this time means for specific hardware:

The database is experimental (a few records in each table - < 100) running on Windows 7 localhost, Intel I7 3,4GHz, 16GB ram, PostgreSQL 9.3.4 (default installation)

Answer 1

Simpler with DISTINCT ON:

SELECT DISTINCT ON (m.id)
       m.*, d.datetime, d.title AS title_details
FROM   master m
LEFT   JOIN details d ON d.master_id = m.id
ORDER  BY m.id, d.datetime DESC;

Assuming master.id to be the primary key and details.datetime to be NOT NULL.
Detailed explanation:
Select first row in each GROUP BY group?

Careful if datetime can be NULL. You probably want NULLS LAST in that case.