How to select latest rows with the same 3 other columns-CodePudding

I have this table that I got using:

SELECT 
    timestamp, metric_name, instance_id, value_sum, unit, instance
FROM
    tbl_metrics
WHERE 
    (metric_name = 'Disk Available' OR metric_name = 'Disk Percent Used') 
    AND instance != ''
ORDER BY 
    timestamp DESC

How do I select just the latest row where the metric_name, instance_id, and instance are unique?

Thanks!

CodePudding user response：

I found out that MAX() gets the max value of the column in the group when used with GROUP BY. So, what I had to do was group the columns that I wanted unique, then add MAX() to the timestamp to effectively get the latest result

SELECT MAX(timestamp) as timestamp, metric_name, instance_id, instance
FROM tbl_metrics
WHERE (metric_name = 'Disk Available' or metric_name = 'Disk Percent Used') and instance != ''
GROUP BY metric_name, instance_id, instance

Then from there, I just joined the table to itself to grab the other columns that I wanted

SELECT a.timestamp, b.metric_name, b.instance_id, a.value_sum, a.unit, b.instance
FROM tbl_metrics a
JOIN (
    SELECT MAX(timestamp) as timestamp, metric_name, instance_id, instance
    FROM tbl_metrics
    WHERE (metric_name = 'Disk Available' or metric_name = 'Disk Percent Used' or metric_name = 'Disk Total') and instance != ''
    GROUP BY metric_name, instance_id, instance
) b
ON ((a.timestamp = b.timestamp) and (a.metric_name = b.metric_name) and (a.instance_id = b.instance_id) and (a.instance = b.instance))
ORDER BY a.timestamp