Order by / table join?

move3rd

Hi,

I'm trying to output forum topics in order of latest posted using my date column (timestamp) from my forum_posts table.

Here is what I'm got so far but it doesn’t seem to be working:

SELECT DISTINCT 
  `forum_topics`.`forum_id`,
  `forum_topics`.`topic`,
  `forum_topics`.`date`,
  `forum_topics`.`views`,
  `forum_topics`.`id`
FROM
  `forum_topics`
  JOIN `forum_posts` ON (`forum_topics`.`id` = `forum_posts`.`topic_id`)
ORDER BY
  `forum_posts`.`date` DESC

lucasrd

SELECT DISTINCT
ft.forum_id,
ft.topic,
ft.date,
ft.views,
ft.id
FROM
forum_topics as ft
LEFT JOIN forum_posts as fp
USING(topic_id)
ORDER BY
fp.date DESC

lucasrd

I forgot one thing in the last query (the GROUP BY clause)

SELECT
ft.forum_id,
ft.topic,
ft.date,
ft.views,
ft.id
FROM
forum_topics as ft
LEFT JOIN forum_posts as fp
USING(topic_id)
GROUP BY ft.forum_id
ORDER BY
fp.date DESC

move3rd

Thanks lucasrd,

I modified your example slightly becouse of of the way the topic id's are named.

SELECT DISTINCT
ft.id,
ft.topic,
ft.date,
ft.views,
ft.forum_id
FROM
forum_topics as ft
JOIN forum_posts as fp
ON ft.id = fp.topic_id
GROUP BY ft.id 
ORDER BY fp.date DESC

It's still outputting as it did before though.

For some reason the fp.date doesnt seem to make any differance to the order. I've tried posting new posts in one of my topics but the topic doesnt move to the top. Infact the order does not change at all.

Any idears?

lucasrd

You could try selecting the MAX(ft.date)...

SELECT DISTINCT
ft.id,
ft.topic,
MAX(ft.date),
ft.views,
ft.forum_id
FROM
forum_topics as ft
JOIN forum_posts as fp
ON ft.id = fp.topic_id
GROUP BY ft.id
ORDER BY fp.date DESC

move3rd

Originally posted by lucasrd
You could try selecting the MAX(ft.date)...

SELECT DISTINCT
ft.id,
ft.topic,
MAX(ft.date),
ft.views,
ft.forum_id
FROM
forum_topics as ft
JOIN forum_posts as fp
ON ft.id = fp.topic_id
GROUP BY ft.id
ORDER BY fp.date DESC

Why would I do that?

The ft.date column isn’t relevant since we are the trying to order topics by tp.date.

lucasrd

I meant fp.date

SELECT DISTINCT
ft.id,
ft.topic,
MAX(fp.date),
ft.views,
ft.forum_id
FROM
forum_topics as ft
JOIN forum_posts as fp
ON ft.id = fp.topic_id
GROUP BY ft.id
ORDER BY fp.date DESC

move3rd

Still not working. I've decided to add a new column to forum_topics called "last_post" which I will update each time a new post is added to that topic and use it to order the topics for display.

Thanks again for your help