sorting - mysql query of 2 tables with inner join -
i want join table , table b , display recent entry each truck.
- table columns: ta id,truck_num,serial_num
- table b columns: tb id,serial_num,lat,long,datetime
desired output
truck | datetime | serial_num | lat | long 1 | datestamp | 123 | 1 | -1 2 | datestamp | 456 | 2 | -2
using join query
select a.truck b.lat b.long b.datetime tb b inner join ta a.serial_num=b.serial_num;
i'v tried giving me recent entry's time.
select a.truck b.lat b.long b.datetime tb b inner join ta a.serial_num=b.serial_num b.datetime = (select max(b.datetime) tb tb a.truck_num=truck_num);
this show recent item per truck. if rest of information want show same in rows can put on group clause, if not have join result
select serial_num, max(datetime) tb group serial_num;
this join have if data not equal , cannot put on group clause
select truck_num, lat, long, maxdatetime tb b inner join ta on a.serial_num=b.serial_num inner join (select serial_num, max(datetime) maxdatetime tb group serial_num) c on b.serial_num=c.serial_num , maxdatetime = datetime
Comments
Post a Comment