Mysql – Selecting multiple “most recent by timestamp” in thesql

mysqlsql

I have a table containing log entries for various servers. I need to create a view with the most recent (by time) log entry for each idServer.

mysql> describe serverLog;
+----------+-----------+------+-----+-------------------+----------------+
| Field    | Type      | Null | Key | Default           | Extra          |
+----------+-----------+------+-----+-------------------+----------------+
| idLog    | int(11)   | NO   | PRI | NULL              | auto_increment | 
| idServer | int(11)   | NO   | MUL | NULL              |                | 
| time     | timestamp | NO   |     | CURRENT_TIMESTAMP |                | 
| text     | text      | NO   |     | NULL              |                | 
+----------+-----------+------+-----+-------------------+----------------+

mysql> select * from serverLog;
+-------+----------+---------------------+------------+
| idLog | idServer | time                | text       |
+-------+----------+---------------------+------------+
|     1 |        1 | 2009-12-01 15:50:27 | log line 2 | 
|     2 |        1 | 2009-12-01 15:50:32 | log line 1 | 
|     3 |        3 | 2009-12-01 15:51:43 | log line 3 | 
|     4 |        1 | 2009-12-01 10:20:30 | log line 0 | 
+-------+----------+---------------------+------------+

What makes this difficult (for me) is:

Entries for earlier dates/times may be inserted later, so I can't just rely on idLog.
timestamps are not unique, so I need to use idLog as a tiebreaker for "latest".

I can get the result I want using a subquery, but I can't put a subquery into a view. Also, I hear that subquery performance sucks in MySQL.

mysql> SELECT * FROM (
    SELECT * FROM serverLog ORDER BY time DESC, idLog DESC
    ) q GROUP BY idServer;
+-------+----------+---------------------+------------+
| idLog | idServer | time                | text       |
+-------+----------+---------------------+------------+
|     2 |        1 | 2009-12-01 15:50:32 | log line 1 | 
|     3 |        3 | 2009-12-01 15:51:43 | log line 3 | 
+-------+----------+---------------------+------------+

What is the correct way to write my view?

Best Solution

I recommend using:

CREATE OR REPLACE VIEW vw_your_view AS
  SELECT t.*
    FROM SERVERLOG t
    JOIN (SELECT sl.idserver,
                 MAX(sl.time) 'max_time'
            FROM SERVERLOG sl
        GROUP BY sl.idserver) x ON x.idserver = t.idserver
                               AND x.max_time = t.time

Never define an ORDER BY in a VIEW, because there's no guarantee that the order you specify is needed for every time you use the view.

Related Solutions

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Best Solution

Related Solutions

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

Mysql – concatenate multiple MySQL rows into one field

Related Question