sorting - mysql query of 2 tables with inner join -


i want join table , table b , display recent entry each truck.

  1. table columns: ta id,truck_num,serial_num
  2. 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

Popular posts from this blog

mysql - Dreamhost PyCharm Django Python 3 Launching a Site -

java - Sending SMS with SMSLib and Web Services -

java - How to resolve The method toString() in the type Object is not applicable for the arguments (InputStream) -