How do you run an "order by" and then "replace" string, but keep the order? Context – I need the string "column_name" to be in the first line, hence using "zzz_column_name" to force it in the order by. And then I need to use replace
to change it from "zzz_column_naem" to "column_name".
SELECT replace(column_name, 'zzz_', '')
FROM (
SELECT *
FROM (
SELECT 'zzz_column_name' AS column_name
UNION
SELECT column_name
FROM table
) s
ORDER BY column_name DESC
) a
After the replace
in the first line, I’d lose the order achieved by order by
.
2
Answers
Just
order by
the unmodified column. You don’t even need a subquery:Note:
UNION ALL
is more efficient thanUNION
– use it unless you have good reasons not toI am wondering if you are actually trying to put first the row that has the fixed value; in that case, we can simplify the whole thing with just a conditional sort. Assuming a table like
mytable(col)
, we can add a header row with value'my_header'
like so:This puts the header row first, followed by all values in order.
If you mind the additional column, we can remove it with a subquery:
You don’t need the outer SELECT and you can sort by the original column name