how to distinct and choose latest rows in group by in Joined Table

Question

My ERD looks like LINK and IMG

I'm making a chat-list-screen API and I want to serve chatmate's name(user.name) and a message's recent content(message.content) to a client.

So I made a query on MySQL.

select u.name, m.content
FROM chat_room as c
    INNER JOIN message as m on m.sender_no = c.user_type_2 or c.user_type_2 = m.reciver_no
    INNER JOIN user as u on u.user_no= c.user_type_2
WHERE c.user_type_1 = 7
GROUP BY u.name

However it doesn't select the latest message.content when new row stacks on the message table.

message
--------
message_no|chat_room_no|sender_no|reciver_no|.   content.  | timestamp
------------------------------------------------------------------------
    1.   |.    1.     |.   7.    |.   8.     |test message1 | 2022.08.30 18:00
    2.   |.    1.     |.   8.    |.   7.     |test message2 | 2022.08.30 19:00
    3.   |.    2.     |.   7.    |.   9.     |test message3 | 2022.08.30 20:00
    4.   |.    2.     |.   7.    |.   9.     |test message4 | 2022.08.30 21:00
    5.   |.    3.     |.   7.    |.   10.    |test message5 | 2022.08.30 22:00

chat_room
--------
chat_room_no|user_type_1|user_type_2|
---------------------------------------
    1.      |.   7.    |.   8.     |
    2.      |.   7.    |.   9.     |
    3.      |    7.    |.   10.    |

user
--------
user_no|name|
-----------------
   7.  |testuser7|
   8.  |testuser8|
   9.  |testuser9|
  10.  |testuser10|

Query result

0|  name   |   content
---------------------------
1|testname10|test message 5
2|testname9|test message 3
3|testname8|test message 1

Desired result
(I want to check the latest message data.)

0|   name  |    content
-------------------------
1|testname10|test message 5
2|testname9|test message 4
3|testname8|test message 2

How can I solve this problem?

The ORDER BY in the subquery makes no difference at all. If you want to order the result, put the ORDER BY at the end of the outer query. — jarlh, Aug 31 '22 at 18:28
Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Aug 31 '22 at 23:00
[Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) An ERD is an image of DDL. PS [mre] — philipxy, Sep 01 '22 at 08:42
Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. And bad code doesn't tell us what you wish it would do. [ask] [Help] — philipxy, Sep 01 '22 at 08:48
Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is ?](https://meta.stackoverflow.com/q/261592/3404097) PS Please clarify via edits, not comments. — philipxy, Sep 01 '22 at 08:48

dognose · Answer 1 · 2022-08-31T20:16:55.127

The Problem is that you are using Group BY u.name without providing an aggregation for the content column.

So, by design, when there are multiple results for content, mysql could return any of these two.

Since it is not really possible to aggregate strings, you need to tell mysql which entry to use.

i.e. without grouping, the result might be:

testname8 | test message1 
testname8 | test message2

So, grouping by name could either return

testname8 | test message1

or

testname8 | test message2

because the only correct solution would be to use group_concat on content to get

testname8 | test message1, test message2

This is a common problem, when trying to get the most recent rows of a table. There are various solutions possible, using subqueries or joins. My favourite is the "join the table with itself" approach:

How can I select rows with most recent timestamp for each key value?

But: I would pick one of the other solutions in the link above. One chatroom has most likely thausands of messages - so, for example if i is exactly 1000, joining with itself on chatroom id would produce a 1000x1000 temporary result set just to fetch a single row.

how to distinct and choose latest rows in group by in Joined Table

1 Answers1