CODEWITHSUNDEEP
mysqljoin
Jeroen
Jeroen

Reputation: 33

MYSQL - join 2 tables - sort in time

In my database I have 2 Tables. The first Table (t1) contain general information (id, address etc.). The second Table (t2) contains measurements (id, timestamp, measurement value).

I am trying to query the database in order to get the lastest measurements for each Id.

To illustrate I created t1 and t2 and run the following:

SELECT          t2.val, t2.id, t2.ts, t1.address 
FROM            t2 
INNER JOIN      t1  
   ON           t1.id = t2.id
GROUP BY        t2.id
ORDER BY        t2.ts desc;

Here the result is that I do NOT get the latest records listed. E.g. I get the following (not the latest).

Result from the above query

The latest records stored in the database are one month later. How to get per Id the latest record while using JOIN to get the Address from t1?

Thanks

Upvotes: 0

Views: 29

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

try to find a bigger time, if you can't you have the latest time

SELECT          t2.val, t2.id, t2.ts, t1.address 
FROM            t2 
INNER JOIN      t1  
   ON           t1.id = t2.id
LEFT JOIN       t2 as times
   ON           t2.ts < times.ts
  AND           t2.id = times.id
WHERE times.ts is NULL

Upvotes: 2

Related Questions