SELECT bus.bus_id, bus.product_type_id, bus.delivery_to_id, histcond.condition, histcond.change_date_condition, histcondmax.max_change_date_condition, histroute.route_name, histroute.change_date_route, histroutemax.max_change_date_route FROM bus_info as bus 
		LEFT JOIN product_class as cls ON bus.class_id = cls.class_id
		LEFT JOIN delivery_to as del ON bus.delivery_to_id=del.delivery_to_id
		LEFT JOIN (SELECT his.product_id, his.condition_id AS cond_id, cnd.condition, his.change_date as change_date_condition
					FROM history_condition as his
					LEFT JOIN present_condition as cnd ON his.condition_id=cnd.condition_id
					INNER JOIN (SELECT product_id, MAX(change_date) AS change_date FROM history_condition WHERE date( change_date ) <= '2015-05-16' GROUP BY product_id) as max
					ON his.product_id=max.product_id AND his.change_date = max.change_date ORDER BY his.product_id)
		as histcond ON bus.bus_id=histcond.product_id
		
		LEFT JOIN (SELECT his.product_id, his.change_date as max_change_date_condition
					FROM history_condition as his
					LEFT JOIN present_condition as cnd ON his.condition_id=cnd.condition_id
					INNER JOIN (SELECT product_id, MAX(change_date) AS change_date FROM history_condition GROUP BY product_id) as max
					ON his.product_id=max.product_id AND his.change_date = max.change_date ORDER BY his.product_id)
		as histcondmax ON bus.bus_id=histcondmax.product_id

		LEFT JOIN (SELECT his.product_id, his.route_id AS rtd_id, rt.route_name, his.change_date as change_date_route
					FROM history_route as his
					LEFT JOIN route as rt ON his.route_id=rt.route_id
					INNER JOIN (SELECT product_id, MAX(change_date) AS change_date FROM history_route WHERE date( change_date ) <= '2015-05-16' GROUP BY product_id) as max
					ON his.product_id=max.product_id AND his.change_date = max.change_date ORDER BY his.product_id)
		as histroute ON bus.bus_id=histroute.product_id
		
		LEFT JOIN (SELECT his.product_id, his.change_date as max_change_date_route
					FROM history_route as his
					LEFT JOIN route as rtd ON his.route_id=rtd.route_id
					INNER JOIN (SELECT product_id, MAX(change_date) AS change_date FROM history_route GROUP BY product_id) as max
					ON his.product_id=max.product_id AND his.change_date = max.change_date ORDER BY his.product_id)
		as histroutemax ON bus.bus_id=histroutemax.product_id
		
		WHERE (bus.delivery_to_id = '6' AND bus.bus_status='Active') ORDER BY CASE histcond.cond_id WHEN 2 THEN 1 WHEN 3 THEN 2 WHEN 4 THEN 3 WHEN 1 THEN 4 WHEN 5 THEN 5 WHEN 6 THEN 6 WHEN 7 THEN 7 ELSE 8 END