douchen4547
2019-08-03 04:43
浏览 82
已采纳

sql group by user HAVING time ASC ORDER BY time DESC

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

enter image description here

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

图片转代码服务由CSDN问答提供 功能建议

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“;  
   
 
 

它可以工作,但它显示用户消息 from the ,然后按顺序由我如何做 \ n

  $ 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“;?
   
 
 

我想你们现在找我了,怎么解决?

我的表结构

我需要选择来自每个用户的最后一条消息 time ASC 而不是后面的 time DESC

  $ 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“; 
   
 
 

但不起作用

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • drvpv7995 2019-08-03 04:51
    已采纳

    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.

    已采纳该答案
    打赏 评论
  • dpw70180 2019-08-03 11:28

    You fix this by writing the query correctly. The correct version would look like this:

    SELECT m.*
    FROM messages m
    WHERE :usr IN (m.to_user, m.from_user) AND
          m.time = (SELECT MAX(m2.time)
                    FROM messages m2
                    WHERE :usr IN (m2.to_user, m2.from_user)
                   );
    

    Or, because you seem to want one row:

    SELECT m.*
    FROM messages m
    WHERE :usr IN (m.to_user, m.from_user) 
    ORDER BY m.time DESC
    LIMIT 1;
    
    打赏 评论

相关推荐 更多相似问题