CODEWITHSUNDEEP
mysqlsql
Tom
Tom

Reputation: 927

Order By Latest Message From Table MYSQL

I have this existing MYSQL query that I want to tweak but I don't know how. I want to tweak it in order for topic to be ordered by the latest message posted on each individual topic.

Here is what I currently have:

select
  t.*,
  coalesce(s.StarCount, 0) as StarCount,
  coalesce(m.UserCount, 0) as UserCount,
  coalesce(m.MessageCount, 0) as MessageCount
from
  Topics t
  left join (
    select 
      topic, 
      count(distinct user) as UserCount,
      count(*) as MessageCount
     from Messages
     group by topic
  ) m ON m.topic = t.topic
  left join (
   select 
    topic, 
    count(*) as StarCount
   from Stars_Given
   group by topic
 ) s ON s.topic = t.topic
ORDER BY UserCount DESC, topicID DESC
LIMIT 0,15

The best answer will be this code but adjusted.

And here is a sample of my tables.

Messages

 MessageID    User      Message      Topic    Time
 1            Tom       Hi           ball     9:00am
 2            John      Hey          book     10:00am
 3            Mike      Sup          book     8:00am
 4            Mike      Ok           book     11:00am

Topics

 topicID    Topic      Title     Category1    Category2
 1          ball       Sports    Action       Hot
 2          book       School    Study        Hot

Stars_Given

 starID     Topic
 1          ball
 2          book
 3          book
 4          book

I want to end up with:

Topic_Review

 Topic    Title     StarCount    UserCount    MessageCount
 book     school    3            2            3
 ball     Sports    1            1            1

Note: The topic of book was the latest topic used in the Messages table so that topic is first.

Upvotes: 0

Views: 98

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Your query is almost there. You just need to add the maximum time stamp into the messages subquery. This is the maxt column in the m subquery:

select t.*, coalesce(s.StarCount, 0) as StarCount, coalesce(m.UserCount, 0) as UserCount,
       coalesce(m.MessageCount, 0) as MessageCount
from Topics t left join
     (select topic, count(distinct user) as UserCount, count(*) as MessageCount,
             max(time) as maxt
     from Messages
     group by topic
    ) m
    ON m.topic = t.topic left join
    (select topic, count(*) as StarCount
     from Stars_Given
     group by topic
    ) s
    ON s.topic = t.topic
ORDER BY maxt desc, UserCount DESC, topicID DESC
LIMIT 0, 15;

Upvotes: 1

Related Questions