skip to Main Content

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


  1. 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
    
    Login or Signup to reply.
  2. 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
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search