CODEWITHSUNDEEP
mysqlderived-table
Antony
Antony

Reputation: 4364

Mysql derived table order by

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?

Upvotes: 0

Views: 407

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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.

Upvotes: 1

Related Questions