I want the highest value to be on the top of the list

Question

So I have this query and display the value, the 'time' is integer, it is often updated by clicking and when it is updated I want the highest value si on the top of the list when page is refresh, but it's not, I wonder if the grouping affects the query?

$sql= "select * FROM message 
       where userid_to = '$UserLoggedIn' || userid_from = '$UserLoggedIn' 
       group by conversation_id 
       ORDER BY time DESC";

Putting parentheses around the assignment s might help. The || has higher precedence than assignment. — UndoingTech, Oct 04 '17 at 01:17
The above query will give you only one row per conversation_id. So you will get one time per conversation_id. Are you using the group by on conversation_id to eliminate duplicates? — Valli, Oct 04 '17 at 01:26
Your query is incorrect because you are aggregating by `conversation_id` but then you select all columns. — Tim Biegeleisen, Oct 04 '17 at 01:27
see this answer https://stackoverflow.com/a/9797138/4020264. especially the important update. — 1010, Oct 04 '17 at 01:28

score 0 · Accepted Answer · answered Oct 04 '17 at 01:30

I think you should be doing the aggregation on conversations inside a subquery to find the most recent time for each conversation. Then join back to your message table to obtain the full message record. I'm not sure what went wrong with your ordering, but your current query is not deterministic.

SELECT m1.*
FROM message m1
INNER JOIN
(
    SELECT conversation_id, MAX(time) AS max_time
    FROM message
    WHERE userid_to = '$UserLoggedIn' OR userid_from = '$UserLoggedIn' 
    GROUP BY conversation_id
) m2
    ON m1.conversation_id = m2.conversation_id AND
       m1.time = m2.max_time
ORDER BY
    m1.time DESC;

score 0 · Answer 2 · answered Oct 04 '17 at 01:59

0

I would try doing it this way:

SELECT *,  MAX(time) as time
FROM message 
WHERE userid_to = '$UserLoggedIn' OR userid_from = '$UserLoggedIn'
GROUP BY conversation_id;

answered Oct 04 '17 at 01:59

CodeGodie

12,116
6
37
66

I wouldn't do it this way, becausd this query would not even run under certain configurations of MySQL (or on most other databases). – Tim Biegeleisen Oct 04 '17 at 02:12
It should work on a standard configured MySQL DB. Can you provide examples to show me under what configurations this would not work? – CodeGodie Oct 04 '17 at 02:52
Look into [ONLY_FULL_GROUP_BY](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by) mode. – Tim Biegeleisen Oct 04 '17 at 03:08
Will do. Thanks. – CodeGodie Oct 04 '17 at 03:14

I want the highest value to be on the top of the list

2 Answers2