Select the ORDER BY ASC|DESC according to a value-CodePudding

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?

CodePudding user response：

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)

CodePudding user response：

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