Getting max date from a list of rows

Yii 2.0 General Discussions
1

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

2

If I’ve understood you correctly, you are trying to find:

The last created messages for each order

If that’s the case, this can be achieved joining a subquery like this:

SELECT order_id, MAX(created_at) FROM message GROUP BY order_id

You join it like the following:

SELECT * FROM message AS msg JOIN (
    SELECT order_id, MAX(created_at) FROM message GROUP BY order_id
) AS sub ON (sub.order_id = msg.order_id)
ORDER BY created_at DESC;

I just got a little confused, because in your first query you are filtering by an order_id, which my query wouldn’t make sense.

3

Hi! sorry for the late reply. I have tried something similar but there is a problem here, and I’m not sure how to solve it. I have tried using a ‘MAX(created_at)’ before, but it seems that it is taking any value for the other fields and the MAX value for created_at in the results. For example, I can have as a result 25 messages from the same order. Each message has a different content, and different created_at values, while order_id stays the same. Using MAX(created_at) it will return a the MAX(created_at) value from all the rows, while the other fields may be the value from any field, not necessarily the row with the MAX(created_at). Not sure if I explained myself well. Am I doing something wrong?

4

The subquery will retrieve only the ID of the last message, which would be used to filter the main query.
So its a strange behavior you are having.

Try adding a “ORDER BY created_at DESC” also in the subquery. But this still dosn’t seem to be the problem

5

For some reason it gets the earliest ID. I have tried both ORDER BY created_at DESC in subquery and also in main query. Also tried mixing both and playing with it. Does not seem to work. Group By seems to always get the earliest ID. Not sure why.

6

Beg your pardon, change the query to this:

SELECT * FROM message AS msg JOIN (
    SELECT order_id, MAX(created_at) FROM message GROUP BY order_id
) AS sub ON (sub.order_id = msg.order_id AND sub.created_at = msg.created_at)
ORDER BY created_at DESC;

I’ve added a condition to the join that I had forgotten.

7

Hey! thanks, I will come back to this if this other thing I am doing does not work.

It seems to be working on MySQL, but I am still translating it into yii2’s query builder.

8

What I was trying worked, but with hundreds of thousands or millions of rows it becomes really slow (seems that never finishes). So I’ve tried this.

SELECT * FROM message AS msg JOIN (
    SELECT order_id, MAX(created_at) as max_date FROM message GROUP BY order_id
) AS sub ON (sub.order_id = msg.order_id AND sub.max_date = msg.created_at)

I modified slightly.
It is working as expected, thank you. Though it is slower than the other solution for small values, for bigger values it is actually finishing in less than 10 seconds. Though 10 seconds might be too much though. But this is working for big values, so thanks :smiley:

1 Like
9

Thanks for sharing!