CODEWITHSUNDEEP
androidsqlsqlite
PPartisan
PPartisan

Reputation: 8231

Inner Join two tables and return greatest value from second table for each entry in first table

How would I construct a query in SQLite that selects every row from a TABLE1, and also selects one row from a TABLE2 where:

Here is the table arrangement I have right now. The value in the r_identifier column corresponds to the t_id value in TABLE1:

TABLE1 ("Tanks") 

+------+--------------+-------+
| t_id |    t_name    | t_vol |
+------+--------------+-------+
|    1 | A Tank       |    23 |
|    2 | Another Tank |    48 |
+------+--------------+-------+

TABLE2("Readings")

+------+--------------+--------+---------+
| r_id | r_identifier | r_date | r_value |
+------+--------------+--------+---------+
|    0 |            1 |   5000 |       5 |
|    1 |            1 |   6000 |       7 |
|    2 |            2 |   7000 |       4 |
|    3 |            1 |   8000 |       3 |
+------+--------------+--------+---------+

And here is the table I would like to return from my query. Because there are multiple entries with the r_identifier of 1, only the one with the highest value in r_date is returned:

+------+--------------+-------+------+--------------+--------+---------+
| t_id |    t_name    | t_vol | r_id | r_identifier | r_date | r_value |
+------+--------------+-------+------+--------------+--------+---------+
|    1 | A Tank       |    23 |    3 |            1 |   8000 |       5 |
|    2 | Another Tank |    48 |    2 |            2 |   7000 |       4 |
+------+--------------+-------+------+--------------+--------+---------+

The closest I've been able to manage so far is with the following statement, inspired by this answer:

SELECT t.*, r.* FROM t INNER JOIN r ON t._id=r_identifier ORDER BY r_date DESC LIMIT 1

This returns the correct values, but for only one "tank" - the first one in the table.

Edit I forgot to mention in my original question that I wanted to retrieve every value in TABLE1 even if there were no entries in TABLE2 that had a matching value in the r_identifier column. Using Gordon Linoff's answer as a guide though I was able to come up with the following which is working well:

SELECT t.*, r.* 
FROM t LEFT OUTER JOIN 
r ON t._id=r_identifier 
WHERE 
    (r_date IS NULL) OR 
    (r_date=(SELECT MAX(r2.r_date) 
    FROM r r2 
    WHERE r.r_identifier=r2.r_identifier));

Upvotes: 1

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

One method is to use a WHERE clause with a correlated subquery to get the maximum date:

SELECT t.*, r.*
FROM t INNER JOIN
     r
     ON t._id = r.r_identifier
WHERE r.r_date = (SELECT MAX(r2.r_date)
                  FROM r r2
                  WHERE r.r_identifier = r2.r_identifier
                 );

Upvotes: 1

Related Questions