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

Order SQL Query by multiple cases, in ASC and DESC – Mysql

I have a problem with this stored procedure in a local database, in SQL. This procedure as is informs a list of all employees, ordered by their id (legajo) or name(nombre_empleado), based on a user input which is a numeric parameter(Orden).
I am trying to order the list by id or name in DESCENDING order (when Orden= 2 or 4), but I can´t figure out how to do it. I am using phpMyAdmin

BEGIN
SELECT * FROM empleado AS e
      INNER JOIN funcion AS f ON f.id_funcion = e.id_funcion
      INNER JOIN turno AS t ON t.id_turno = e.id_turno
      INNER JOIN convenio AS c ON c.id_convenio = e.id_convenio
ORDER BY
    (CASE
     WHEN Orden = 1 THEN legajo
     WHEN Orden = 3 THEN nombre_empleado
     WHEN Orden = 2 THEN legajo
     WHEN Orden = 4 THEN nombre_empleado
     END);
END

I tried:
-Adding DESC in both

WHEN Orden = 2 THEN legajo DESC
WHEN Orden = 4 THEN nombre_empleado DESC

-Making two separate CASEs

  CASE
     WHEN Orden = 1 THEN legajo
     WHEN Orden = 3 THEN nombre_empleado
     END ASC,
  CASE
     WHEN Orden = 2 THEN legajo
     WHEN Orden = 4 THEN nombre_empleado
     END DESC

-Adding ELSEIF

None works, as I get an SQL syntax error. Is there a way around it? What am I doing wrong? Thanks!

Tags:

2

Answers


  1. 0

    A case expression is going to generate a single column of data, in this case a temporary column that is used for ordering. As such, it can’t contain both a number and a string as mixing types in a column isn’t supported. Furthermore, casting the id/int to a string would cause mysql to perform lexicographical ordering, which is probably not what you want for your id column.

    Instead you could sort by ID and Name separately:

    ORDER BY 
     CASE WHEN Orden IN (2,4) THEN 0 ELSE legajo END,
     nombre_empleado

    That will set the id in this temporary order by column to 0 when Orden is 2 or 4 so those records get sorted before any legajo where the orden is 1or3. Then for all of those 0records, it will take a second sort ofnombre_empleado. If you want the orden in (2,4)records sorted last, then you could change that0to a number higher than anylegajo` in your table.

    Login or Signup to reply.
  2. 0

    I think I would take a calculated (virtual column) for this and use that to order things out. Like;

    SELECT *, 
@order_column := IF(order IN (2, 4), 'id', 'name') AS order_column, 
IF(@order_column = 'id', 1, 2) AS order_type_column
FROM my_table
ORDER BY order_column IF(order_type_column = 1, 'ASC', 'DESC')

    You may need to adjust the order_type_column with some trial and error, but that is the very basic idea.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search