oracle sql union order by-CodePudding

This is a certification question case A and case B, why it works in case B, but doesn't work in case A

case A (not working)
select col_a, col_b, 'b' from table_a
union
select col_a, col_b, 'a' from table_a
order by 'b';

case B (working)
select col_a, col_b, 'b' from table_a order by 'b';

CodePudding user response：

'b' is a string literal and not an identifier for the column (as the identifier for the column is the quoted identifier "'B'").

You can either use the column number:

select col_a, col_b, 'b' from table_a union
select col_a, col_b, 'a' from table_a
order by 3;

or use a quoted identifier and convert the literal to upper-case:

select col_a, col_b, 'b' from table_a union
select col_a, col_b, 'a' from table_a
order by "'B'";

or give the column an alias and use that:

select col_a, col_b, 'b' AS b from table_a union
select col_a, col_b, 'a' from table_a
order by b;

CodePudding user response：

The 'b' is a string literal and order by string literal will only work if all the rows have the same value for that column which is the string literal.