SQL order by and then replace-CodePudding

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.

CodePudding user response：

Just order by the unmodified column. You don't even need a subquery:

SELECT replace(column_name, 'string', '') AS column_name_replaced
FROM (
    SELECT 'zzz_column_name' AS column_name
    UNION ALL
    SELECT column_name FROM table
) s
ORDER BY column_name DESC

Note: UNION ALL is more efficient than UNION - use it unless you have good reasons not to

I 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:

SELECT 'my_header' AS col, 1 AS is_header
UNION ALL
SELECT col, 0 FROM mytable
ORDER BY is_header DESC, col

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:

SELECT col
FROM (
    SELECT 'my_header' AS col, 1 AS is_header
    UNION ALL
    SELECT col, 0 FROM mytable
) t
ORDER BY is_header DESC, col

CodePudding user response：

You don't need the outer SELECT and you can sort by the original column name

SELECT replace(column_name, 'string', '')
FROM (
    SELECT 'zzz_column_name' AS column_name
    UNION
    SELECT column_name
    FROM table
) s
ORDER BY column_name DESC