CODEWITHSUNDEEP
mysql
Jacob
Jacob

Reputation: 3801

MySQL: Get row with highest timestamp with group by of multple values

Given the following schema:

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| timestamp           | int(11)      | NO   | MUL | 0       |                |
| col1                | varchar(255) | YES  | MUL |         |                |
| col2                | varchar(255) | YES  |     |         |                |
.....

+---------------------+--------------+------+-----+---------+----------------+

I can do the following query:

SELECT * from report_data GROUP BY concat(host_name, ";", service_description)

And I would get one row for each unique col1 and col2 combination. It seems it always returns the first row (i.e lowest id) that has the combination.

However in my dataset, I have multiple rows with unique col1 and col2 combinations, and I want the ones with the highest timestamp. The timestamps might be out of order, that is the record with the highest ID might not necessarily have the highest timestamp.

How would I achieve that?

Sample data:

+-----+------------+-----------+------------------------+
| id  | timestamp  | col1      | col2                   |
+-----+------------+-----------+------------------------+
| 916 | 1          | val1      | valA                   |
| 915 | 2          | val2      | valB                   |
| 914 | 5          | val1      | valB                   |
| 913 | 4          | val1      | valA                   |
+-----+------------+-----------+------------------------+

In the above example I would like to have these rows back (there are two occurrences where col1 and col2 is the same, the one with the highest timestamp is returned)

+-----+------------+-----------+------------------------+
| id  | timestamp  | col1      | col2                   |
+-----+------------+-----------+------------------------+
| 915 | 2          | val2      | valB                   |
| 914 | 5          | val1      | valB                   |
| 913 | 4          | val1      | valA                   |
+-----+------------+-----------+------------------------+

Upvotes: 0

Views: 35

Answers (1)

xQbert
xQbert

Reputation: 35333

Supported in most version of mySQL: newer version we could use window/analytical functions to do this...

SELECT A.ID, A.timestamp, A.col1, A.col2
FROM report_data A
INNER JOIN (SELECT max(timestamp) TS, col1, col2
            FROM report_Data
            GROUP BY COl1, Col2) B
 on A.timestamp = B.TS
and A.Col1 = B.col1
and A.Col2 = B.Col2

With 8.0 + of mySQL

WITH CTE AS (SELECT A.*, Row_number() over (partition by Col1, col2 order by Timestamp Desc) RN
             FROM report_data)
SELECT * 
FROM CTE
INNER JOIN report_data B
 on CTE.ID B.ID
WHERE CTE.RN = 1

Upvotes: 2

Related Questions