Ascending and descending by column value

Question

I have a table EMPLOYEEwith EMP_ID, NAMEand DEPARTMENT_ID.

I want to order the whole record with an odd DEPARTMENT_IDhow ASCand even DEPARTMENT_IDhow DESC.

Can this be done?

thanks

Answer 1

You can use CASEin ORDER BY, change the sign accordingly:

...
ORDER BY CASE WHEN DEPARTMENT_ID  % 2 = 0
         THEN -DEPARTMENT_ID   
         ELSE  DEPARTMENT_ID END ASC;

Answer 2

It makes two queries, first filters the coefficients of the second pair. Order as desired, and then request Union.

SELECT e.* FROM (SELECT *
FROM EMPLOYEE
WHERE MOD(DEPARTMENT_ID, 2) = 1
ORDER BY DEPARTMENT_ID ASC) e
UNION ALL
SELECT e1.* FROM (SELECT *
FROM EMPLOYEE
WHERE MOD(DEPARTMENT_ID, 2) = 0
ORDER BY DEPARTMENT_ID DESC) e1

Answer 3

try it

SELECT *
FROM EMPLOYEE
ORDER BY CASE WHEN DEPARTMENT_ID % 2 = 1 then 1 else 2 end,DEPARTMENT_ID