How to use join statement to select the latest recorded value from another table?

Question

I am trying to retrieve the latest recorded value of a column in a table, using another table as reference.

i.e:

Table1
        |id|Tnumid|
         13    1
         14    2
         15    3
         16    4
Table2
        |id|Tnumid|PRnum|Timestamp
         16    1   422   1455815894
         17    2   560   1455815895 
         18    2   890   1455815896
         19    3   450   1455815897
         20    4   700   1455815898

Basically I want to use Table1 as reference, which contains a column value called Tnumid that never repeats (similarly to the id, but Tnumid doesn't skip a value). I want to retrieve the latest recorded value of PRnum on the second table.

Basically the output I want in the example above would be:

    |id||Tnumid|Prnum|
     16    1     422
     18    2     890
     19    3     450
     20    4     700

Note how the latest recorded value is chosen in the case of Tnum=2.

So I want to SELECT PRnum from Table2, but I need to retrieve exactly the number of values of Tnumid, always choosing the latest recorded value. How can do this with a JOIN statement or something similar...? I am new to MySQL. Thanks a lot.

SELECT DISTINCT `Tnumid`, `id`, Prnum FROM `table 2` ORDER BY `id` DESC — Murad Hasan, Mar 09 '16 at 11:51
Check out the SECOND answer in this SO Question - http://stackoverflow.com/questions/18694094/select-only-rows-by-join-tables-max-value which quotes the MySQL documentation. — Vitani, Mar 09 '16 at 11:52

score 0 · Answer 1 · answered Mar 09 '16 at 11:52

0

select max(t1.id),t1.tnumid,max(t2.prnum)
from table1 t1
inner join table2 t2 on t1.tnumid =t2.tnumid
group by t1.tnumid

answered Mar 09 '16 at 11:52

Ankit Agrawal

2,426
1
13
27

Gordon Linoff · Answer 2 · 2016-03-09T12:18:55.403

You can pre-aggregate the data and join the results together:

select t2.*
from table2 t2 join
     (select trumid, max(id) as maxid
      from table2
      group by t2
     ) tt2
     on tt2.trumid = t.trumid;

Another method does this in the where clause:

select t2.*
from table2 t2
where t2.id = (select max(tt2.id) from table tt2 where tt2.trumid = t2.trumid);

Note that the first table is not needed for this query.

EDIT:

If some values in table1 are missing, you can get them using a left join. For instance:

select t2.id, trumid, t2.prnum
from table1 t1 left join
     table2 t2
     using (trumid)
where t2.id = (select max(tt2.id) from table tt2 where tt2.trumid = t2.trumid) or
      t2.id is null;

Maybe you need a left join with table 1 if you have values in table 1 that correspond to no rows in table 2 and you want to highlight these cases — Lelio Faieta, Mar 09 '16 at 11:58

score 0 · Answer 3 · answered Mar 09 '16 at 13:00

with lastvalue as
(
  select * ,row_number() over(PARTITION by tnumid order by id ) as laestval from Table_2 
)
  select * from lastvalue as cte  where laestval =(select max (laestval) from lastvalue where tnumid =cte.Tnumid)

Hope it will work for you

How to use join statement to select the latest recorded value from another table?

3 Answers3