PPartisan PPartisan - 1 month ago 12
SQL Question

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:


  • There is a match between an
    id
    value from
    TABLE1
    , and;

  • If there is more than one
    id
    match in
    TABLE2
    , only the highest value in another
    DATE
    column is returned.



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.

Answer

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
                 );