This is my sample tables:
Table1:
ID Runnum Thickness Date
1 2 1.23 1/8/2015
2 2 2.32 1/9/2015
3 3 2.21 1/9/2015
4 4 3.12 1/8/2015
Table2 :
ID Runnum Insitu
1 2 1.23
2 3 2.32
3 4 2.21
4 5 3.12
Result i want:
Runnum Insitu Runnum Thickness Date
2 1.23 2 2.32 1/9/2015
3 2.32 3 2.21 1/9/2015
4 2.21 4 3.12 1/8/2015
I want to join table1 and table2 and select the duplicate with the latest records, this is my query so far.
SELECT a.Runnum, a.insitu, b.Runnum, b.thickness
FROM test.dummy_ease a
INNER JOIN (SELECT runnum, thickness, times FROM test.dummy_xrf group by runnum) AS b
ON a.runnum = b.runnum
The values returned from that query are almost right except for the duplicate value it is not returning the latest one.
I assume ID from table1 is set to auto_increment and the recent one is also based in this criteria then you have to self join table1 to the latest data
select a.Runnum,
b.insitu,
b.Runnum Runnumb,
a.Thickness
from Table1 a
join (select max(ID) ID ,Runnum
from Table1
group by Runnum) a1
using(ID,Runnum)
join Table2 b ON a.Runnum = b.Runnum
If recent one depends on recent date criteria then in inner query you have to select max of your date column but from your sample data it looks like you have stored date as varchar type which will not be treated as date object you have to first convert it in to date object using str_to_date then calculate max of date and join your table1 again query will be something like Demo
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments