Question 112792 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL order by and then replace

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.

Tags:

2

Answers


  1. 0

    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. 0

    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