Mysql derived table order by

Question

If I run the following query:

SELECT *
FROM `smp_data_log`
WHERE Post_id = 1234 AND Account_id = 1306
ORDER BY Created_time DESC

I get 7 rows back including entries with the following Created_times:

1) 1424134801

2) 1424134801

3) 1421802001

4) 3601

If I run the following query:

SELECT mytable.*
FROM (SELECT * FROM `smp_data_log` ORDER BY Created_time DESC) AS mytable
WHERE Post_id = 1234 AND Account_id = 1306
GROUP BY Post_id

I am would expect to see 1424134801 come back as a single row - but instead I am seeing 3601?? I would have thought this would have returned the latest time (as its descending). What am I doing wrong?

score 1 · Accepted Answer · answered Feb 19 '15 at 14:24

Your expectation is wrong. And this is well documented in MySQL. You are using an extension, where you have columns in the select that are not in the group by -- a very bad habit and one that doesn't work in other databases (except in some very special circumstances allowed by the ANSI standard).

Just use join to get what you really want:

SELECT l.*
FROM smp_data_log l JOIN
     (select post_id, max(created_time) as maxct
      from smp_data_log
      group by post_id
     ) lmax
     on lmax.post_id = l.post_id and lmax.maxct = l.created_time;

Here is the quote from the documentation:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

Thank you for your response Gordon. I've added "WHERE l.Post_id = 1234 AND l.Account_id = 1306" and I still get 2 rows returned? The only difference is the Primary ID. — Antony, Feb 19 '15 at 14:36
I see why - its because I have two rows with the same time/date stamp tieing in. — Antony, Feb 19 '15 at 14:53
@Antony . . . If you have an auto-incrementing primary key, you might want to use that instead of `created_time`. — Gordon Linoff, Feb 19 '15 at 23:04

Mysql derived table order by

1 Answers1