CODEWITHSUNDEEP
mysqlsubquerysql-order-by
JRsz
JRsz

Reputation: 2941

MySQL order by sub-select result

I want to dynamically order a dataset with a sub-select query after the order by clause. To get myself familiar with this I created a small test set and tried various things from which neither worked.

To directly see the result I try to produce an error by ordering by a column that does not exist. This is my query and the result:

select * from entries order by 2 desc, (select case when (1=1) then 4 else 5 end);
select * from entries order by 2 desc, (select if(1=1,4,5));
+----+------+---------+------+
| id | name | content | time |
+----+------+---------+------+
| 19 | ccc  | ccc     |  300 |
| 18 | bbb  | bbb     |  200 |
| 17 | aaa  | aaa     |  100 |
| 20 | aaa  | aaa     |  400 |
| 21 | aaa  | aaa     |   50 |
+----+------+---------+------+

Switching from 1=1 to 1=2 does not alter the result in any way. I expected an error with 1=2 but nothing happened. Even in this form both queries should order be the 4th column, which they dont.

I expect that the dataset is ordered by the forth column so it would look like this:

+----+------+---------+------+
| id | name | content | time |
+----+------+---------+------+
| 19 | ccc  | ccc     |  300 |
| 18 | bbb  | bbb     |  200 |
| 21 | aaa  | aaa     |   50 |
| 17 | aaa  | aaa     |  100 |
| 20 | aaa  | aaa     |  400 |
+----+------+---------+------+

Upvotes: 0

Views: 374

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You really should not use the positional syntax for order by -- I even think it is deprecated in the ANSI standard. An integer -- by itself -- represents a column by position. However, in expressions with numbers, the values are, well, just numbers.

So, include the column names explicitly:

order by name desc,
         (case when (1=1) then time else ?? end)

There is no reason to have select before the case.

Upvotes: 1

Related Questions