I know this is not strictly yii2 related, but I am trying to use it with yii anyways.
But I am trying to do the following
SELECT * FROM message where order_id=1 order by created_at desc;
This is returning me all messages that belongs to that specific order, ordered by the creation date (descending).
I am trying to get only the maximum date value. I have tried using Group by order_id, but this will group it, and then it takes the first value (oldest). Can’t seem to find a way to reverse that, no matter if I change order by, it simply takes the oldest one, I assume that is because group by goes before order by. I have also tried setting ‘Select … MAX(creation_date)…’, but this will return the values from the oldest row, except its creation date, which will return the max value, but I need it to return the row where the creation_date is max.
Is there a way to achieve this?
I must mention that the complete query includes many filters and conditions. Also I don’t need only one order, but a list of orders and the last message of each order. So the data provider will be full of:
id - message - order_id_1 - creation_date
id - message - order_id_2 - creation_date
id - message - order_id_3 - creation_date
id - message - order_id_4 - creation_date
any help is appreciated