CODEWITHSUNDEEP
mysqlsqlsql-order-by
viv
viv

Reputation: 6177

How does ORDER BY work with single-value expressions in MySQL?

I have a problem with order by. We know that if we give integer in order by then it orders via index of column. When I use it this way every time I execute query, I get same result, which is correct.

But when I make this value the result of an expression (I mean I use a expression which always evaluates to 1), then I get 3 possible combinations of result when I execute it.

So for same value which is static, same result; but for same value which is an evaluated expression, different results.

Below is the example of query which I am referring to:

SELECT SomeColumns, (rand()* 10) as rand
FROM TableName
ORDER BY (if ((rand in (1) ), 1,1))
LIMIT 0 , 6

The above query gives three possible result sets.

Upvotes: 3

Views: 845

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272166

Look at the SELECT statement syntax; ORDER BY clause is defined as:

ORDER BY {col_name | expr | position}

Later on this page it is mentioned:

Column positions are integers and begin with 1

Sort by column position works as expected when it is specified as an integer literal. In your case, ORDER BY (if ((rand in (1) ), 1,1)) is an expression, not a column position. It works as follows:

  • The rand column is added to all rows that contains values between 0.0 and 9.9999...
  • The expression if(rand in (1), 1, 1) is evaluated for each row as follows:
    • The sub-expression rand in (1) almost always returns false because there the chances of the rand column being exactly 1 are very, very, very low
    • Depending on the previous expression the IF condition evaluates to 1 or 1 (perhaps it was a typo in your code)
    • The whole expression evaluates to 1 for each row
  • Since the expression in ORDER BY clause yields 1 for each row, the rows will not be sorted

Upvotes: 1

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385204

ORDER BY's syntax is:

ORDER BY {col_name | expr | position} [ASC | DESC], ...

Although the following would appear to the human-eye to be equivalent:

ORDER BY 1
ORDER BY (if ((rand in (1) ), 1,1))

they are not.

  • The first one sorts by the 1st column's data, as it's an integer with value 1.

  • The second one, although it evaluates to 1, is not an integer but an expression and, as such, a different sorting mechanism is invoked. For each row, the expression 1 is evaluated and used to determine the row's position; because your expression actually doesn't refer to row data at all, your results will be completely unpredictable.

    Whether you'll get the table's "natural order" (that is, the physical ordering that happens to be present underneath the relational abstractions) or some random-looking order that the ORDER BY created I couldn't say, but it also doesn't matter.

Usually you'd write something like this for an expression:

ORDER BY `col` % 6

... i.e. something that references data.

Upvotes: 1

Nikson Kanti Paul
Nikson Kanti Paul

Reputation: 3440

ORDER BY (if ((rand in (1) ), 1,1))

it ordered your result set based on the expression. for dynamic value it also reordered based on your order by expression

Upvotes: 0

Related Questions