How to get joined entrys last when grouping by some columns

Question

my table is filled with private messages if i GROUP BY to_id i just get the oldest entry i need the newest entry first

here is what i tried...

SELECT t1.date
     , t2.nick
     , t1.to_id
     , t3.message
     , xis_checked ,COUNT(*) AS subject
  FROM private_messages t1 
  LEFT 
  JOIN private_messages_text t3
    ON t1.private_messages_text_id = t3.id 
  LEFT 
  JOIN members t2 
    ON t1.members_id = t2.id 
 WHERE members_id = '1027' 
 GROUP 
    BY t1.to_id DESC

i hope you understood what i tried

the newer entries have a checked value of 0 and that's what i need, The date should also be the last in table for the specified members_id

i forgot the COUNT(*) in my question if i group then he shows all entries

score 0 · Answer 1 · answered May 01 '20 at 16:11

You should use ORDER BY

SELECT t1.date,t2.nick,t1.to_id,t3.message,xis_checked FROM private_messages t1 LEFT JOIN private_messages_text t3
ON t1.private_messages_text_id=t3.id LEFT JOIN members t2 
ON t1.members_id=t2.id WHERE members_id='1027' 
ORDER BY t1.to_id DESC

score 0 · Answer 2 · answered May 01 '20 at 16:26

0

Order BY help us here!

//query
GROUP BY t1.to_id
ORDER BY t1.date DESC

Hope it helps.

answered May 01 '20 at 16:26

Harisudha

527
5
5

found solution with `max(date)` and `min(checked)` – Asario May 01 '20 at 16:51
Supr. max(date) results in one entry. – Harisudha May 02 '20 at 08:37

How to get joined entrys last when grouping by some columns

2 Answers2