0

I have a database table which looks like this:

messages_table:

transmitter_ename:    varchar(40)
message:              varchar(600)
timestampt:           datetime
read:                 boolean

Is there any option to select the 10 newest messages from different users in a sql statement?

Fernando Carvalhosa
  • 1,098
  • 1
  • 15
  • 23
Armin Rahimzadeh
  • 1
  • 2
  • "from different users in a sql statement" - Multiple different users in a *single* SQL statement? Or a statement to get the 10 newest messages for some given user? – JimmyB Feb 04 '15 at 16:23
  • @HannoBinder For example ... I have multiple messages from 30 different users... and I want to get the 10 newest messages from 10 different users... I know that i could programm a loop in php to filter it out but I thought there is maybe a solution to get it in one table with a select statement! – Armin Rahimzadeh Feb 04 '15 at 16:27
  • 1
    Have you [tried this?](http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) Looks like a duplicate question to me – Fernando Carvalhosa Feb 04 '15 at 16:28
  • @FernandoCarvalhosa Oh yes really... this was what I was looking for but I haven't found it. Many thanks :) – Armin Rahimzadeh Feb 04 '15 at 16:31
  • If that fully answer your question, please delete your question here to avoid being flagged as a duplicate. Otherwise, post your own answer – Fernando Carvalhosa Feb 04 '15 at 16:33

1 Answers1

-1
SELECT 
  *
FROM
  (SELECT * FROM messages_table as mt ORDER BY mt.timestampt DESC) AS messages_table
GROUP BY 
  messages_table.transmitter_ename
LIMIT 10;
Ahmet Erkan ÇELİK
  • 2,364
  • 1
  • 26
  • 28
  • 1
    Wouldn't this only get you the first message for each user (because of the `GROUP BY`)? – Fernando Carvalhosa Feb 04 '15 at 16:43
  • It will get a random message for each user and this is courtesy of MySQL to the developers. The query is not valid SQL. It works on MySQL but it produces unpredictable results. A quote from the [documentation](http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html): *"In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want."* – axiac Feb 04 '15 at 16:47
  • Fernando Carvalhosa, your comment is not correct. Because ORDER BY will first run! I've tested, my answer is correct! – Ahmet Erkan ÇELİK Feb 04 '15 at 16:51
  • @AhmetErkanÇELİK it's not about the `ORDER BY`. The `GROUP BY` will return only one record for each `transmitter_ename`(regardless of the `LIMIT`number). As @axiac mentioned, the result might even be random (ignoring your `ORDER BY`). I will test this later today when i get home – Fernando Carvalhosa Feb 04 '15 at 17:33
  • While the content of table `messages_table` doesn't change, the query will return the same result set. But it's possible to delete a row from the table then insert it again and this query will produce a different result even if the content of the table `messages_table` is the same as before. – axiac Feb 04 '15 at 17:59
  • @Fernando Carvalhosa, yes you are correct. I've fixed my answer :) – Ahmet Erkan ÇELİK Feb 04 '15 at 18:04