One table, three column MYSQL query question

Question

In reference to my previous question: http://stackoverflow.com/questions/3082817/one-table-two-column-mysql-query-question

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

returns the expected results:

192.168.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.

Answer 1

MAX(`timestamp`)

Answer 2

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.

Answer 3

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

Answer 4

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