order by if not 0

adw999

I have a forum database that i need to order by last post and topic start date. Now ordering with dates isn't a problem but the database has a 'lastpost' field in the topics table which holds the id of the most recent post for the topic (if there isn't one [by default] its set to 0). I need to order the sql results by last activity, therefore a mixture of the lastpost field and 'startdate' (when the topic was started).

Basically i need to order like any forum software does, just using a mixture of 'lastpost' and 'startdate'. I thought something along the lines of:

"SELECT t.id AS `id`, t.title AS `title`, t.date AS `date`, t.uid AS `uid`, t.pinned AS `pinned`, t.open AS `open`, p.date AS `postdate`, SUBSTRING_INDEX(t.content, ' ', 25) AS `stuff` FROM `topics` t, `posts` p WHERE t.tid='".$id."' AND t.lastpost=p.id ORDER BY t.pinned DESC,  p.date, t.date DESC LIMIT ".$from.", ".$to."

But it doesn't work.. any ideas?

NogDog

Not 100% sure I follow, but if I do, I think what you could do is something like this (using hypothetical column names):

SELECT <other fields>, IF(lastpost_id > 0, lastpost_time, topic_start_time) AS sort_time 
FROM . . . ORDER BY sort_time DESC . . .