gistfile1.sqf

SELECT
  main_cat.id,
  main_cat.name,
  last_thread.thread_id,
  last_thread.name,
  last_thread.transliteratename,
  last_thread.posted_at,
  last_thread.user_id,
  last_thread.username,
  counts.thread_count
FROM category main_cat LEFT OUTER JOIN
  (SELECT
     threads.id thread_id,
     threads.name,
     threads.category,
     threads.transliteratename,
     threads.posted_at,
     users.id   user_id,
     users.username
   FROM threads
     INNER JOIN (SELECT MAX(id) max_id
                 FROM threads
                 GROUP BY threads.category) unique_threads ON unique_threads.max_id = threads.id
     LEFT OUTER JOIN users ON users.id = threads.userid) last_thread ON last_thread.category = main_cat.id
  LEFT OUTER JOIN (SELECT
                     COUNT(thread.id) thread_count,
                     thread.category  cat
                   FROM threads thread
                   GROUP BY thread.category) AS counts
    ON counts.cat = main_cat.id