can anyone help me in a query. I want to select some rows which are grouped by thread_id column. I want to select only those rows which have latest date.. let me provide an example
thread_id | message_id | msg_date
tid_1 | mid_1 | 2018-01-10
tid_1 | mid_2 | 2018-01-14
tid_1 | mid_3 | 2018-01-13
tid_2 | mid_1 | 2018-01-11
tid_2 | mid_2 | 2018-01-12
I want a query which select all records from table but group them by thread_id and row with the latest date should be chosen. Expected output:
thread_id | message_id | msg_date
tid_1 | mid_2 | 2018-01-14
tid_2 | mid_2 | 2018-01-12
I also have a msg_date_timestamp column in my table. would be better if we order messages on the basis of msg_date_timestamp . what I have tried so far is:
SELECT * FROM emails WHERE user_id='".$email."' AND mailbox_label='INBOX' GROUP BY thread_id ORDER BY msg_date_ts DESC
And
SELECT * FROM emails WHERE user_id='$email' AND mailbox_label='INBOX' AND msg_date_ts in (SELECT MAX(msg_date_ts) from emails GROUP BY thread_id) ORDER BY msg_date_ts DESC
Nothing worked as I expected. Also I want to get the count of messages in a thread. for example in thread tid_1, it should return 3 and for tid_2, it should return 2 . I want thread count in same query.. (not a separate query for count)
Please help me. really stucked.
Thanks!
