MySQL ORDER BY – Using CASE with Multiple Order Criteria

caseMySQLorder-by

I would like to know how to use multiple order columnes with case. For example case 1, order by FirstName, GivenName case 2 order by GivenName, FirstName.

I tried this, but it looks like the colon introduce an error

Order by case
when 1 then FirstName, GivenName
else GivenName, FirstName end

Best Answer

Case is supposed to return one value, not a tuple. Beside, you should compare 1 with something. If you want conditional ordering you can do that with two case statements:

ORDER BY CASE WHEN x = 1 THEN FirstName ELSE GivenName END
       , CASE WHEN x = 1 THEN GivenName ELSE FirstName END

Related Solutions

Sql-server – What are the alternatives for an ORDER BY clause in a View

There is no such thing as an "ordered" view because a view is supposed to be a set of rows. Use a stored procedure if you want a fixed ordering.

T-sql – CASE ORDER BY with multiple columns, and different sort options

Break it out a little more:

ORDER BY CASE WHEN @orderby = 1 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END ASC,
         CASE WHEN @orderby = 2 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END DESC,
         tp.lastname ASC,
         tp.firstname ASC

You only need the sort order to change on the first field, so don't enclose the others in the CASE.

It should be noted that we don't include an ELSE for each CASE, which means any other value will return NULL and be discarded from the ORDER BY.

Best Answer

Related Solutions

Sql-server – What are the alternatives for an ORDER BY clause in a View

T-sql – CASE ORDER BY with multiple columns, and different sort options

Related Question