CODEWITHSUNDEEP
phpmysqlsql
john zapanta
john zapanta

Reputation: 13

Retrieve latest timestamp row from table using INNER JOIN

want to retrieve the latest timestamp of the row of name from table. However only the table "location" has the timestamp. I used INNER JOIN to join the tables "elderly1" and "location" together. i am only able to show that i have retrieved the latest timestamp but not the latest "latitude" and "longitude" from the table "location". Please help.

Query

SELECT e.name,e.illness, e.patient_id,e.patient_image,e.area, e.arduino_mac,
       l.arduino_mac, l.latitude, l.longitude,MAX(l.timestamp) as ts 
FROM elderly1 e 
JOIN location l 
    on e.arduino_mac = l.arduino_mac 
WHERE e.arduino_mac = l.arduino_mac 
GROUP BY e.name 
ORDER BY l.timestamp

Upvotes: 1

Views: 1469

Answers (2)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

It's difficult to say much without knowing the candidate keys of each table, but in general you must make sure that the SELECT clause is functionally dependent of the GROUP BY clause. Given you formulation of the problem I would suggest something like:

SELECT e.name,e.illness, e.patient_id,e.patient_image,e.area, e.arduino_mac,
       l.arduino_mac, l.latitude, l.longitude, l.timestamp as ts 
FROM elderly1 e 
JOIN ( SELECT l1.arduino_mac, l1.latitude, l1.longitude, l1.timestamp
       FROM location l1
       WHERE timestamp = ( SELECT MAX(timestamp)
                           FROM LOCATION l2
                           WHERE l1.arduino_mac = l2.arduino_mac )
) as l
    on e.arduino_mac = l.arduino_mac 
ORDER BY l.timestamp

Upvotes: 1

spencer7593
spencer7593

Reputation: 108400

If the (arduino_mac,timestamp) tuple is unique in the location table, you could do something like this:

SELECT e.name
     , e.illness
     , e.patient_id
     , e.patient_image
     , e.area
     , e.arduino_mac
     , l.arduino_mac
     , l.latitude
     , l.longitude
     , l.timestamp
  FROM elderly1 e
  JOIN ( SELECT d.arduino_mac
              , MAX(d.timestamp) AS latest_ts
           FROM location d
          GROUP BU d.arduino_mac
       ) f
    ON f.arduino_mac = e.arduino_mac
  JOIN location l
    ON l.arduino_mac = f.arduino_mac
   AND l.timestamp   = f.lastest_ts
 GROUP BY e.name
 ORDER BY l.timestamp

The inline view f gets the "latest timestamp" for each value of arduino_mac. (Performance of the view query will be best if there's a suitable index available, e.g.

  ... ON location (arduino_mac,timestamp)

We can use that value of timestamp to retrieve the other columns on that row, with a join to the location table.

Note that if there are two (or more) rows with the same latest "timestamp" value for a given arduino_mac, then this query will retrieve all of the rows with that matching timestamp, and it will be indeterminate which of those rows will remain after the GROUP BY operation. (If we're guaranteed (arduino_mac,timestamp) is unique, this won't be an issue. In the more general case.)

In the same way, if there are multiple rows with the same name value in elderly, with different values of arduino_mac, it's indeterminate which of the matching location rows is retrieved, and returned.

Upvotes: 1

Related Questions