MySQL : Select subquery is NOT working with Group By

Question

I got this query:

SELECT * FROM (
      SELECT 
      messages.* 
    FROM 
      messages
    ORDER BY 
      messages.created_at DESC
) as messages GROUP BY messages.contact_id

the timestamp it returned is still WRONG and not DESC:

before:

| id | contact_id | message | created_at          |
|  1 | 4          | ....    | 2023-03-10 19:48:06 |
|  2 | 3          | ....    | 2023-03-10 19:48:06 |
|  3 | 3          | ....    | 2023-03-11 19:48:06 |
|  4 | 4          | ....    | 2023-03-11 19:48:06 |

after:

| id | contact_id | message |  created_at          |
|  2 | 3          | ....    |  2023-03-10 19:48:06 |
|  1 | 4          | ....    |  2023-03-10 19:48:06 |

does anyone know why?

Why would you group by the id? There's probably only one record per id. Also, why the subselect? Can you show wat your tables look like and what you're actually trying to query? — realbart, Mar 13 '23 at 06:26
You have to apply the `order by` on the outermost resultset. Order by in subqueries is useless (and even forbidden in some dbms) — derpirscher, Mar 13 '23 at 06:38
And if you select a column which is not functionally dependent on the grouping criterion (ie you have different dates for the same contacting) you will get one arbitrary value of them — derpirscher, Mar 13 '23 at 06:41
What sense does that group by even make? You don't have any aggregation function... — derpirscher, Mar 13 '23 at 06:42
the outer `order by` is useless and does nothing because the `group by` take precedence before the outer `order by` — yeln, Mar 13 '23 at 06:44
@Tushar the `before` is the database rows before the running the query `after` is the result after the query — yeln, Mar 13 '23 at 06:45
@derpirscher I use `group by` because I need to get a list of latest messages by contacts, thats why I need `order by timestamp DESC` for latest single message per contact — yeln, Mar 13 '23 at 06:46
`after` is the result after the query == Is that your expected output ? It will be helpful to answer, if you can post expected output. In `after` the timestamps of both created_at are identical — Tushar, Mar 13 '23 at 06:53
ORDER BY applied in the subquery **is always lost** in outer query. If you need minimal `created_at` value per group then use `MIN()` function. — Akina, Mar 13 '23 at 06:53
You are wanting the latest messages row for each group. That's more complicated than just a group by/order by. — ysth, Mar 13 '23 at 07:02

score 0 · Answer 1 · answered Mar 13 '23 at 08:30

Assumption: The greater the id, the latest the date

You get the max id and created datetime first

SELECT messages.* FROM
messages
INNER JOIN
    (SELECT MAX(id) as id, contact_id, MAX(created_at)
    FROM messages
    GROUP BY contact_id
    )as test ON messages.id = test.id

Please refer to this db fiddle

MySQL : Select subquery is NOT working with Group By

1 Answers1