yesterday yesterday - 1 month ago 10
MySQL Question

order table output based on table itself and different table

I have a question, I'm building a forum and I am stuck with the following problem.

I have 2 tables

table: topics

topic_id | name | timestamp | pinned
-------------------------------------------------
1 | topic_1 | 2016-01-01 12:00:00 | 0
2 | topic_2 | 2016-01-01 13:00:00 | 0
3 | topic_3 | 2016-01-01 14:00:00 | 1
4 | topic_4 | 2016-01-01 15:00:00 | 0
5 | topic_5 | 2016-01-01 16:00:00 | 0

table: messages

message_id | topic_id | text | timestamp
---------------------------------------------------
1 | 1 | test | 2016-01-01 12:30:00
2 | 2 | test | 2016-01-01 13:30:00
3 | 2 | test | 2016-01-01 13:45:00
4 | 1 | test | 2016-01-01 14:30:00
5 | 4 | test | 2016-01-01 17:30:00


As you can see, the topic #1 has 2 messages, topic #2 also 2 messages, topic #4 1 messages, topics #3 and #5 no messages at all.

I want to order them on PINNED and TIMESTAMP. How?

-> If a topic is pinned, it MUST BE at the top

-> If the topic has message(s), order them on the timestamp of the LAST message

-> If the topic does not have any messages, order them on the timstamp of the topic itself

Example above, the order would be (from top to bottom):

topic_3 (pinned so ALWAYS at the top)
topic_4 (last message at 17:00:00)
topic_5 (no messages but started at 16:00:00)
topic_1 (last message at 14:30:00)
topic_2 (last message at 13:45:00)


I hope my question is clear enough.

Hope someone can help!

Thanks in advance.

EDIT:

I tried the following but this won't work

SELECT DISTINCT
t.topic_id,
t.name
FROM
topics AS t
LEFT JOIN messages AS m ON m.topic_id = t.topic_id
ORDER BY
t.pinned DESC,
m.timestamp DESC,
t.timestamp DESC

Answer

Your task is complicated by the fact that there are many messages attached to one topic, and you need to use the message having MAX(timestamp) for sorting. DISTINCT won't work as it takes a random record out of the table.

I had to use a subquery to solve the problem. IFNULL will return message timestamp if it is not NULL, otherwise - topic timestamp as required by the task.

SELECT
        t.topic_id,
        t.name,
        IFNULL(m.timestamp, t.timestamp)
FROM 
        topics AS t 
LEFT JOIN
(SELECT
    messages.topic_id,
    MAX(messages.timestamp) as timestamp
FROM
    messages
GROUP BY
    messages.topic_id
) AS m
  ON m.topic_id = t.topic_id
GROUP BY
      t.topic_id
ORDER BY 
        t.pinned DESC, 
        IFNULL(m.timestamp, t.timestamp) DESC;

Output:

+----------+---------+----------------------------------+
| topic_id | name    | IFNULL(m.timestamp, t.timestamp) |
+----------+---------+----------------------------------+
|        3 | topic_3 | 2016-01-01 14:00:00              |
|        4 | topic_4 | 2016-01-01 17:30:00              |
|        5 | topic_5 | 2016-01-01 16:00:00              |
|        1 | topic_1 | 2016-01-01 14:30:00              |
|        2 | topic_2 | 2016-01-01 13:45:00              |
+----------+---------+----------------------------------+

Remove the timestamp if necessary, I used to show that the query fetches the correct record.