select max(p2.final_price),p.products_id, p.products_model, pd.products_name, m.manufacturers_name, p.products_quantity, 
    p.products_image, p.products_weight, p.manufacturers_id, p.products_tax_class_id, p.abra_unit, 
    final_price, order_count, view_count, abra_sc_id, abra_srm_id 
from listing_table_products p 
join listing_table_products as p2 on p.products_id=p2.products_id
atd.........................