Only sorting by order entered, not by timestamp

Asked Feb 01 '21 at 11:58

Active Feb 01 '21 at 22:46

Viewed 29 times

I cannot ORDER BY the dt_contacted field. No matter which way I have done this, it keeps on listing in the order it was entered by.

SELECT leads.*, historical.history
FROM leads
    LEFT JOIN (SELECT lead_id,
                    group_concat( concat( DATE_FORMAT(dt_contacted,"%m-%d-%Y %h:%i %p"),": ",action," ",note,"\n" ) SEPARATOR " " )
                        AS history                     
                FROM (SELECT * 
                        FROM lead_actions 
                        ORDER BY TIMESTAMP(dt_contacted) DESC) hist
                GROUP BY lead_id               
            ) AS historical
    ON leads.id = historical.lead_id
WHERE leads.id = :button_id

edited Feb 01 '21 at 22:46

Rick James

135,179
13
127
222

asked Feb 01 '21 at 11:58

Scott Weisberg

2

`ORDER BY` clauses in subqueries without `LIMIT` are in general ignored by the query engine. You need to have the `ORDER BY` clause on the outer query to have a guaranteed effect on the results. – Nick Feb 01 '21 at 12:03
Is this a "groupwise-maximum" problem? – Rick James Feb 01 '21 at 22:46
@Nick, The LIMIT did the trick. Thank you! – Scott Weisberg Feb 03 '21 at 15:03

Only sorting by order entered, not by timestamp

0 Answers0