sql - SELECT from one table, JOIN on another table, ORDER BY a third table? -

i have 3 tables forum application creating:

• boards - represents collection of posts, is_deleted boolean
• posts - represents text collection of comments
• comments - represents text

my goal display number of posts boards not marked deleted, ordered last comment made post (and when post created). current query have is:

select   posts.*,   (select created_date    comments    comments.post_id = posts.id    order comments.created_date desc    limit 1) last_comment posts join boards on boards.id = posts.board_id boards.is_deleted = 0 order   last_comment desc,   posts.created_date desc limit 4 offset 0 

which works well, don't need last_comment other when use order results, feels i'm running additional select little reason. there better way this?

edit 2013-05-18:

fooling around bit more, believe have query doesn't rely on nested select statements:

select   posts.* posts join boards   on boards.id = posts.board_id left outer join comments   on posts.id = comments.post_id   boards.is_deleted = 0 group   posts.id order   comments.created_date desc,   posts.created_date desc limit 4 offset 0 

at time there no better way via select statement.

you put select statement on different position, like:

select p.*, com.created_date posts p, comments com, boards b b.id = p.board_id , b.is_deleted = 0 , com.id in (select c.id comments c c.post_id = p.id  order c.created_date desc limit 1) order com.created_date desc, p.created_date desc  limit 4 offset 0; 

query planners designed optimize where-clause, hence if possible try make select statements in where-clause. if without second select statement, can if store information in posts-table @ moment data saved. have add column posts-table, instance name 'last_comment' , put creation time of comment in field. in case can replace second statement ordering of field 'last_comment' in posts table. however, change table due functional requirement might change in future. not advise that. each table or view different object , sqls take care of ever changing functional requirements.