One table, three column MYSQL query question

In reference to my previous question: One table, two column MYSQL query challenge

The command: "SELECT hostname, GROUP_CONCAT(DISTINCT name) as name FROM comments GROUP BY hostname"

returns the expected results:

192.16开发者_StackOverflow社区8.0.1 user1,user2

192.168.3.5 user3

The same table "comments" also has a "timestamp" field. How do I include the latest (most current) timestamp for each user using that ip?

Example:

192.168.0.1 user1-1277146500,user2-1277250087

192.168.3.5 user3-1237150048

I tried a number of variations of the command, but could only get all the timestamps, not the most current one...

also.. if possible it would be nice to convert the epoch timestamp to something more friendly before displaying it.

---

Concat the varchar converted Max(timestamp) to the name and add timestamp in the Group by clause

SELECT hostname,
  GROUP_CONCAT(DISTINCT name, CAST(MAX('timestamp') AS VARCHAR) as name 
FROM comments GROUP BY hostname

Think it will work, though I have not tested this.

---

SELECT 
     hostname, 
     GROUP_CONCAT(CONCAT_WS("-", name, m_timestamp))
FROM
     (SELECT 
          hostname, 
          name,
          cast(max(timestamp) as char(24)) as m_timestamp 
     FROM 
          comments 
     GROUP BY 
          hostname,
          name) as A
GROUP BY 
     hostname

Edit

Updated column name to timestamp (was c_timestamp) per OP's comments

---

MAX(`timestamp`)

---

 SELECT hostname, GROUP_CONCAT(DISTINCT name) as name, max(TIMESTAMP_FIELD) as timestamp_name FROM comments GROUP BY hostname