sql group by user HAVING time ASC ORDER BY time DESC

Question

SQL

$sql = "SELECT DISTINCT from_user, messages.* FROM messages WHERE to_user = :user OR from_user = :usr Group BY from_user 
ORDER BY time DESC"; 

It works but It shows user message from latter , before order by How I could do something like

$sql = "SELECT DISTINCT from_user, messages.* FROM messages WHERE to_user = :user OR from_user = :usr Group BY from_user HAVING time ASC 
ORDER BY time DESC"; 

?

HAVING time ASC ORDER BY time DESC"; ?

I think you guys got me now , how to solve ?

my table structure

I need to select last message from each user by time ASC and than latter order by time DESC I tried with

 $sql = "SELECT DISTINCT from_user, messages.* FROM messages  WHERE to_user = :user OR from_user = :usr ORDER BY time ASC Group BY from_user
ORDER BY time DESC";  

but do not worked

Answer 1

Your issue is in the having clause. It is use with aggregate function to group your record and check some condition on it. for more info you may find this link.

For ordering it is only done by order by clause.

Moreover in your query you are using group by for only 1 field but then message.* will select all fields, which again shows error.

Kindly see the above link and re-structure your query.

  select m.*
  from messages  m
  where m.time = (select max(m2.time) from messages  m2 where m2.to_user = m.to_user and m2.from_user=m.from_user)  
  and m.to_user = :user OR m.from_user = :usr 
  order by time desc;

Note: You can apply filter as per your need. Variant of this may help in your query.