Can You Add an If Statement in Order By

Can you add an if statement in ORDER BY?

Well, you can use the IF function in MySQL (Note the emphasis on function since there's also an unrelated IF statement)...:

ORDER BY IF(TYPE='Member', LNAME, GROUPNAME) ASC

However, in this case it seems the better choice (From a flexibility standpoint) would be the CASE statement:

ORDER BY 
    CASE `type` 
        WHEN 'Member' THEN LNAME 
        WHEN 'Group' THEN GROUPNAME
        ELSE 1 END 
    ASC

Note that the entire block from CASE to END is to be considered as a single "unit". The result of which is what you're trying to sort against (Hence why the ASC comes after the block, rather than inside of it)...

IF statement in ORDER BY Clause of a SELECT Statement in a SQL Server Stored Procedure

ORDER BY
   CASE WHEN @switch = 0 THEN Field1 END,
   CASE WHEN @Switch = 1 THEN Field2 END

T-SQL IF statements in ORDER BY clauses

There's a gotcha here... you can't mix data types in your case statement, so you need to create different groups of case statements for the different data types.

SELECT
    *
FROM
    table
ORDER BY
    CASE WHEN @SortType = id THEN table.Id END ASC,
    CASE WHEN @SortType != id THEN table.Date END ASC

Order of execution for an if with multiple conditionals

This type of evaluation is called short-circuiting.
Once the result is 100% clear, it does not continue evaluating.

This is actually a common programming technique.
For example, in C++ you will often see something like:

if (pX!=null && pX->predicate()) { bla bla bla }

If you changed the order of the conditions, you could be invoking a method on a null pointer and crashing. A similar example in C would use the field of a struct when you have a pointer to that struct.

You could do something similar with or:

if(px==null || pX->isEmpty()} { bla bla bla }

This is also one of the reasons that it is generally a good idea to avoid side effects in an if condition.

For example suppose you have:

if(x==4 && (++y>7) && z==9)

If x is 4, then y will be incremented regardless of the value of z or y, but if x is not 4, it will not be incremented at all.

Use IF statement on ORDER BY

Use multiple clauses in the order by:

ORDER BY type,  -- put numbers first
         (case when type = 1 then value + 0 end) desc, -- convert values to numbers
         value

The problem with using one expression, such as if() or case, is that the result is converted to one type. So, you are sorting either numbers or strings, but not both at the same time.

How use ASC/DESC inside a ORDER BY if statement?

How about ordering by IF(type=2, -ranking, ranking) after ordering by type? Negating the ranking will cause the opposite order for that type.

EDIT

Since ranking is a VARCHAR column, you would need to to convert it to a number to use this technique.

SELECT * FROM test ORDER BY type ASC, CAST(ranking as SIGNED)*IF(type = 1, 1, -1) ASC;

You can test the code directly at sqlfiddle

Order of execution in IF statement when multiple conditions present

The result will be the same, because && is a short-circuiting operator. This means that if the first operand evaluates to false, the second operand will not be evaluated.

What is the effect of ordering if...else if statements by probability?

As a general rule, most if not all Intel CPUs assume forward branches are not taken the first time they see them. See Godbolt's work.

After that, the branch goes into a branch prediction cache, and past behavior is used to inform future branch prediction.

So in a tight loop, the effect of misordering is going to be relatively small. The branch predictor is going to learn which set of branches is most likely, and if you have non-trivial amount of work in the loop the small differences won't add up much.

In general code, most compilers by default (lacking another reason) will order the produced machine code roughly the way you ordered it in your code. Thus if statements are forward branches when they fail.

So you should order your branches in the order of decreasing likelihood to get the best branch prediction from a "first encounter".

A microbenchmark that loops tightly many times over a set of conditions and does trivial work is going to dominated by tiny effects of instruction count and the like, and little in the way of relative branch prediction issues. So in this case you must profile, as rules of thumb won't be reliable.

On top of that, vectorization and many other optimizations apply to tiny tight loops.

So in general code, put most likely code within the if block, and that will result in the fewest un-cached branch prediction misses. In tight loops, follow the general rule to start, and if you need to know more you have little choice but to profile.

Naturally this all goes out the window if some tests are far cheaper than others.

Can You Add an If Statement in Order By