When applying order by twice, hive assumes column selection order is same on both inner & outer query which does order by on wrong column assuming wrong column datatype. You can rewrite your query having outer & inner query projection as same. SELECT t_6.color color, t_6.noq_empty noq_empty FROM (SELECT t_5.color_gbakc2 color, t_5.noq_empty_gbakc3 noq_empty FROM (SELECT testtable.color_gbakc2 color_gbakc2, testtable.noq_empty_gbakc3 noq_empty_gbakc3, testtable.color_gbakc1 color_gbakc1 FROM testtable testtable WHERE testtable.color_gbakc2 IN ('Red', 'Blue', 'Green') ) t_5 WHERE t_5.color_gbakc2 IN ('Red', 'Blue') ORDER BY noq_empty ASC ) t_6 ORDER BY color Fortunately HIVE-6348 is a plan optimizer & its eliminating this issue also. If my answer helped you, accept the answer. It will help others in the community. ... View more