SQL：按消息排序不起作用

Question

why does this SQL Code not run?

user_chats

             id | user_id | to_user_id |  ad_id  | timestamp
----------------+---------+------------+---------+-----------
              1 |       1 |      6     |   13    |  1513516133

user_messages

             id | chat_id |    text    | user_id | timestamp
----------------+---------+------------+---------+-----------
              1 |       1 |    Hello   |    1    |  1513516133
              2 |       1 |    Hi!     |    6    |  1513516754

I want to get the Chats and order them by user_messages.timestamp. My SQL Code is:

SELECT user_chats.id,
       user_chats.timestamp,
       ad_id,
       title,
       user_chats.user_id
FROM   user_chats
       INNER JOIN ads
               ON ads.id = ad_id
WHERE  user_chats.user_id = "1"
        OR user_chats.to_user_id = "1"
ORDER  BY (SELECT id
           FROM   user_messages
           WHERE  chat_id = user_chats.id
           ORDER  BY user_messages.id DESC) 

Answer 1

The issue is that you've used a subquery in your Order By clause: as this returns multiple results for each record in the main query it cannot be used to order the results of the main query.

I think you're trying to order the results by the latest message in each chat, but simply joining the user_messages table will mean you'll get duplicates (each chat being returned once per message). You can get around this by joining to an inline view:

SELECT DISTINCT user_chats.id,
       user_chats.timestamp,
       ad_id,
       title,
       user_chats.user_id
FROM   user_chats
       INNER JOIN ads
               ON ads.id = ad_id
       LEFT JOIN   
         --in line view aliased 'UM' returns one row per chat_id in user_messages, with the last timestamp for that ID
                  (SELECT max(timestamp) LastMessage, 
                  chat_id
                  FROM user_messages
                  GROUP BY chat_id) um
               ON um.chat_id = user_chats.id
WHERE  user_chats.user_id = 1
        OR user_chats.to_user_id = 1
ORDER  BY um.LastMessage desc