1

I have a database with a table called 'messages'. The colums are 'sender', 'reseiver', 'subject', 'time' (the timestamp).

With my query, I want to get the entries grouped by the subject. BUT I want to have the corresponding time to be the latest.

So, for example, if this was my db:

person1   |   person2   |   hello   |   2019-06-24 20:0**7**:00
person2   |   person1   |   hello   |   2019-06-24 20:0**8**:00

I want to get this:

subject: hello
last entry: 2019-06-24 20:0**8**:00

I wasn't able to put the ORDER BY in front of the GROUP BY, so I tried a subquery:

SELECT subject, sender, receiver, time
FROM 
    (SELECT subject, sender, receiver, time
     FROM messages
     ORDER BY time DESC) AS subselect
GROUP BY subject

But it doesn't work and I can't figure out why.

Can somebody help me?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Beragrom
  • 23
  • 3
  • Easy to do in MySQL 8.x. Not that easy in 5.x. Which MySQL version do you have? – The Impaler Jun 24 '19 at 20:06
  • Possible duplicate of [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) – sticky bit Jun 24 '19 at 20:06
  • Im using MySQL 5.7 :P – Beragrom Jun 24 '19 at 20:13
  • No it does not work.. if it executes you play russian roulette with you resultset.. on servers with enabled sql mode only_full_group_by this query will error because you misuse GROUP BY – Raymond Nijland Jun 25 '19 at 08:46

2 Answers2

0

I believe this is what you are looking for:

SELECT subject,sender,reseiver, MAX(time) AS time FROM messages GROUP BY subject

So, it works like this:

  1. Gets relevant records (in this case -- all of them, as no WHERE provided)
  2. Groups them by subject column, so now we have groups of records with the same subject
  3. In every group it looks for max value of time column -- but this is per group, so, I guess that's what you wanted

And if you want to sort groups somehow, just add another ORDER BY at the end, without any subqueries.

alx
  • 2,314
  • 2
  • 18
  • 22
  • No it does not work.. if it executes you play russian roulette with you resultset.. on servers with enabled sql mode only_full_group_by this query will error – Raymond Nijland Jun 24 '19 at 20:31
  • `only_full_group_by` would fail for original query in the qustion too, so, irrelevant. Re roulette, agree, missed that part, I thought for some reason only time matters. – alx Jun 24 '19 at 20:39
  • it's not irrelevant, the topicstarter or futher readers need to know what happens if they execute this kind of queries.. – Raymond Nijland Jun 25 '19 at 08:47
  • there is only one valid execption when this query is valid is when the message columns has a primary or unique key.. Then SQL 1999 standards optional feature which is called functional dependency which MySQL 5.7.5+ supports can be used. – Raymond Nijland Jun 25 '19 at 08:49
0
SELECT subject,sender,reseiver,max(time) 
FROM messages
GROUP BY subject,sender,reseiver
ORDER BY max(time) DESC

You should group by sender,reseiver as well, otherwise those columns have unpredictable value. Or omit those column entirely.

slepic
  • 641
  • 4
  • 11