CODEWITHSUNDEEP
mysqlsql-order-by
lolbas
lolbas

Reputation: 794

MySQL get team chats sorted by last message send time

I have 2 tables, responsible for teams and for team messages. Lets say their structure is like this:

teams
team_id |   team_name
--------+------------
      1 |  First team
      2 | Second team
      3 |  Third team

team_messages
team_message_id | team_id | message_text | send_time
----------------+---------+--------------+----------
              1 |       1 |              |         1
              2 |       3 |              |         2
              3 |       2 |              |         3

The way i want to show teams is:

team_id | team_name
--------+------------
      2 | Second Team
      3 | Third team
      1 | First team

So basically i need to show all teams ordered by last message in that team desc. What i have tried is

SELECT * FROM teams a
ORDER BY
(
    SELECT `send_time`
    FROM team_messages b
    ORDER BY b.`t_message_id` DESC
    LIMIT 1
) DESC

But this seems to give wrong result

Upvotes: 2

Views: 1093

Answers (2)

ain
ain

Reputation: 22759

Your original query selects just one row, the latest message time, for all records. Try something like

SELECT a.*, (
    SELECT max(send_time)
    FROM team_messages b
    WHERE b.team_id = a.team_id
    ) as ord
FROM teams a
ORDER BY ord DESC

You might need to move it into derived table if MySQL doesn't allow alias in order by:

SELECT * FROM (
   SELECT a.*, (
     SELECT max(send_time)
     FROM team_messages b
     WHERE b.team_id = a.team_id
     ) as ord
   FROM teams a
)
ORDER BY ord DESC

Upvotes: 1

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

Try 

select * from teams a join team_messages b on a.team_id = b.team_id
order by b.send_time desc

Upvotes: 1

Related Questions