How to Retrieve the Latest Status for Each Service Using Inner Join and LIMIT?

Selecting from Two Tables with Inner Join and LIMIT

To retrieve the latest status for each service in two tables, Service and Status, an inner join can be utilized.

For reference, the existing tables are:

• Service:

  • Columns:

    • id: Integer
    • name: String

• Status:

  • Columns:

    • id: Integer
    • status: String
    • service_id: Integer
    • timestamp: DateTime

The desired result is the latest status for each service, as seen below:

id	name	status	timestamp
1	Test1	OK	October, 15 2015 09:08:07
2	Test2	OK	October, 15 2015 10:15:23

The previous statement had limitations in retrieving the latest statuses:

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

To ensure the correct selection of the latest status, a subquery is required to identify the maximum timestamp for each service:

SELECT
  service_id, 
  MAX(timestamp) AS MaxDate
FROM status 
GROUP BY service_id

This subquery creates a new table with two columns: service_id and MaxDate. The MaxDate column represents the latest timestamp for each service.

Using this subquery, the main query can be modified to join the Service table with the maximum timestamp table:

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;

By joining on both the service_id and the MaxDate, only the latest status for each service will be displayed, ensuring the desired result.

The above is the detailed content of How to Retrieve the Latest Status for Each Service Using Inner Join and LIMIT?. For more information, please follow other related articles on the PHP Chinese website!