'Select the ORDER BY ASC|DESC according to a value

I want to select the order by type according to a variable.

Something like this: ORDER BY t.name case when @sort = 'asc' then ASC ELSE then DESC END

Is that possible?

mysql


Solution 1:[1]

ORDER BY CASE WHEN @sort = 'ASC'
              THEN t.name
              ELSE 0
              END ASC,
         CASE WHEN @sort = 'DESC'
              THEN t.name
              ELSE 0
              END DESC

Solution 2:[2]

If your version of MySql is 8.0+ use ROW_NUMBER() window function:

ORDER BY CASE 
  WHEN @sort = 'asc' THEN ROW_NUMBER() OVER (ORDER BY t.name)
  ELSE ROW_NUMBER() OVER (ORDER BY t.name DESC)
END

or:

ORDER BY CASE WHEN @sort = 'asc' THEN 1 ELSE -1 END * ROW_NUMBER() OVER (ORDER BY t.name)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Akina
Solution 2 forpas

Related Questions