当前位置:网站首页>Group by gets the latest piece of data with the largest time after grouping (it needs to add a limit to take effect)

Group by gets the latest piece of data with the largest time after grouping (it needs to add a limit to take effect)

2020-11-10 17:46:21 MJBrian

When using GROUP BY grouping , The data returned by default is the smallest record in the group id The smallest data ,

When developing, it is often necessary to group and put the latest data in front ,

In order to achieve the requirements , Using nested queries , Separate use order by Sort by

SELECT
    id,
    from_id,
    to_id,
    conversation_id,
    created_date,
    has_read,
    content
FROM
    ( SELECT * FROM message WHERE from_id = 6 OR to_id = 6 ORDER BY created_date DESC) AS tt 
GROUP BY
    conversation_id 
ORDER BY
    created_date DESC

 

  But the result is still id The smallest piece of data  

 

 

Finally add limit max_size It will take effect after

SELECT
    id,
    from_id,
    to_id,
    conversation_id,
    created_date,
    has_read,
    content
FROM
    ( SELECT * FROM message WHERE from_id = 6 OR to_id = 6 ORDER BY created_date DESC limit 100) AS tt 
GROUP BY
    conversation_id 
ORDER BY
    created_date DESC

 

版权声明
本文为[MJBrian]所创,转载请带上原文链接,感谢