I have two tables Service
and Status
. The service table only saves a name
and an id
| id | name | |----|-------| | 1 | Test1 | | 2 | Test2 |
There is also a state table like this
| id | status | service_id | timestamp | |----|--------|------------|---------------------------| | 1 | OK | 1 | October, 15 2015 09:03:07 | | 2 | OK | 1 | October, 15 2015 09:08:07 | | 3 | OK | 2 | October, 15 2015 10:05:23 | | 4 | OK | 2 | October, 15 2015 10:15:23 |
I want to get such data
| id | name | status | timestamp | |----|-------|--------|---------------------------| | 1 | Test1 | OK | October, 15 2015 09:08:07 | | 2 | Test2 | OK | October, 15 2015 10:15:23 |
Latest status with service data. I have tried this argument
SELECT ser.id, ser.name, a.status, a.timestamp from Service ser inner join (select * from status order by Status.timestamp DESC limit 1) as a on a.service_id = ser.id
but I only get
| id | name | status | timestamp | |----|-------|--------|---------------------------| | 2 | Test2 | OK | October, 15 2015 10:15:23 |
How do I change the statement to get what I want?
For testing SQL Fiddle
P粉0236500142023-12-31 18:20:46
For each service, only use if no subsequent service exists NOT EXISTS
Return status:
select ser.id, ser.name, st.status, st.timestamp from service ser left join status st1 on ser.id = st1.service_id where not exists (select 1 from status st2 where st2.service_id = st1.service_id and st2.timestamp > st1.timestamp)
You can choose to execute LEFT JOIN
to return a service without any status. If not required, switch to JOIN
.
P粉7528260082023-12-31 13:53:21
You can do this:
SELECT ser.id, ser.name, s.status, s.timestamp FROM Service ser INNER JOIN status as s ON s.service_id = ser.id INNER JOIN ( SELECT service_id, MAX(timestamp) AS MaxDate FROM status GROUP BY service_id ) AS a ON a.service_id = s.service_id AND a.MaxDate = s.timestamp;
Join with subquery:
SELECT service_id, MAX(timestamp) AS MaxDate FROM status GROUP BY service_id
Will eliminate all statuses except the latest date.