I have two tables like so:
OBJECT_VIEW table:
| view_id | object_id | object_name |
|---|---|---|
| 1423 | 12 | ob1 |
| 1423 | 15 | ob2 |
| 1423 | 17 | ob3 |
| 2499 | 15 | ob2 |
OBJECT_EXEC table:
| object_id | execution_time |
|---|---|
| 12 | 16-oct-22 00:01:00 |
| 12 | 18-oct-22 00:07:00 |
| 15 | 14-oct-22 00:01:00 |
| 17 | 11-oct-22 00:01:00 |
| 17 | 13-oct-22 00:01:00 |
| 12 | 15-oct-22 00:01:00 |
| 15 | 13-oct-22 00:01:00 |
| 12 | 16-oct-22 00:01:00 |
| 17 | 17-oct-22 00:01:00 |
Id like to get a result where I see the latest execution per object that exist within a view. (The view_id - object_id is a many-to-many relationship.) So only one of each object_id in the resulting table with the max value of execution_time. This needs to also be filtered by view_id, so I can check the max execution times per view.
Like this:
| object_id | object_name | execution_time |
|---|---|---|
| 12 | ob1 | 18-oct-22 00:07:00 |
| 17 | ob3 | 17-oct-22 00:01:00 |
| 15 | ob2 | 14-oct-22 00:01:00 |
So far, I've tried using MAX() and group by, but my output is not showing the max_execution per object_id ONLY. It's showing multiple of the same id. What am I missing?
SELECT ov.object_id, ov.object_name, MAX(oe.execution_time) AS "LAST_EXECUTION"
FROM OBJECT_EXEC oe
JOIN OBJECT_VIEW ov
ON oe.object_id = ov.object_id
WHERE ov.view_id = 1423
GROUP BY oe.execution_time, ov.object_id, ov.object_name;
CodePudding user response：
You should not be including execution_time in the GROUP BY clause:
SELECT ov.object_id, ov.object_name, MAX(oe.execution_time) AS "LAST_EXECUTION"
FROM OBJECT_EXEC oe
INNER JOIN OBJECT_VIEW ov
ON oe.object_id = ov.object_id
WHERE ov.view_id = 1423
GROUP BY ov.object_id, ov.object_name;