mysql - How to use inner join in the same table -

i have table name tnx_line_transfer. schema below

now want generate output below

output

i trying below query not showing proper output. issue on query

select     main_tbl.operator_id,     main_tbl.production_line     tnx_line_transfer main_tbl inner join (     select         operator_id,         max(date) max_date             tnx_line_transfer     group         operator_id ) temp on main_tbl.operator_id = temp.operator_id 

if other alternative easy solution please let me know.thanks in advance.

you missed join date column max_date.

select main_tbl.operator_id,        main_tbl.production_line   tnx_line_transfer main_tbl        inner join (select operator_id,                           max(`date`) max_date                      tnx_line_transfer                    group  operator_id) temp                on main_tbl.operator_id = temp.operator_id                   , main_tbl.max_date = temp.`date` --here 

another way using sub-query

select main_tbl.operator_id,        main_tbl.production_line   tnx_line_transfer main_tbl  `date` = (select max(`date`)                  tnx_line_transfer temp                 main_tbl.operator_id = temp.operator_id)  

row_number concept comes in handy above scenario unfortunately mysql doesnot support window function's