CODEWITHSUNDEEP
mysqlsql
AXAI
AXAI

Reputation: 706

Does the order matter in ORDER BY clause?

in the SQL ORDER BY clause, For a table like this in example.

[ a  -  b   -   c   -  d ]
--------------------------
[ x  -  1   -   i   -  e ]
[ y  -  2   -   k   -  f ]
[ z  -  3   -   m   -  g ]

If i wrote the clause like the following

ORDER BY a ASC, b DESC, c DESC, d ASC

Is there any difference if i changed the order of the columns to somehting like

ORDER BY d DESC, c DESC, b ASC, a ASC

Or does it work the same and doesn't make any difference since it will always pick the exact ORDER for columns where d DESC, c DESC, b ASC, a ASC which is the same as ORDER BY a ASC, b DESC, c DESC, d ASC but in difference order?

Upvotes: 1

Views: 1281

Answers (3)

Dr. X
Dr. X

Reputation: 2920

ORDER BY a ASC, b DESC, c DESC, d ASC means that

  • First Order a in Ascending order
  • Then order b in descending order
  • Then order c in descending order
  • At last order d in Ascending order

In your example it does not matter because values are the same but in below example it senses such as

Example Inordered list suach as; 

| a  |  b  |  c  | d  |
-----------------------
| 10 |  5  |  2  |  7 | 
| 10 |  3  |  1  |  7 | 
| 5  |  5  |  6  |  3 | 
| 4  |  2  |  5  |  3 | 
| 5  |  4  |  6  |  3 |    
| 3  |  5  |  9  |  4 |
| 3  |  6  |  6  |  4 | 
| 5  |  5  |  6  |  3 | 
| 4  |  2  |  5  |  3 | 
| 5  |  4  |  6  |  3 |    
| 3  |  5  |  1  |  4 |
| 3  |  6  |  2  |  4 |

and ORDER BY d ASC, a ASC, b DESC, c DESC;

 | a   |  b |  c | d  |
 -----------------------
 |  4  |  2 |  5 |  3 |
 |  4  |  2 |  5 |  3 |
 |  5  |  5 |  6 |  3 |
 |  5  |  5 |  6 |  3 |
 |  5  |  4 |  6 |  3 |
 |  5  |  4 |  6 |  3 |
 |  3  |  6 |  6 |  4 |
 |  3  |  6 |  2 |  4 |
 |  3  |  5 |  9 |  4 |
 |  3  |  5 |  1 |  4 |
 |  10 |  5 |  2 |  7 |
 |  10 |  3 |  1 |  7 |

then Ordered by after ORDER BY a ASC, b DESC, c DESC, d ASC is

 | a  |  b |  c |  d | 
  --------------------
 | 3  |  6 |  6 |  4 | 
 | 3  |  6 |  6 |  4 | 
 | 3  |  6 |  2 |  4 | 
 | 3  |  5 |  9 |  4 | 
 | 3  |  5 |  1 |  4 | 
 | 4  |  2 |  5 |  3 | 
 | 4  |  2 |  5 |  3 | 
 | 5  |  5 |  6 |  3 | 
 | 5  |  5 |  6 |  3 | 
 | 5  |  4 |  6 |  3 | 
 | 5  |  4 |  6 |  3 | 
 | 10 |  5 |  2 |  7 | 
 | 10 |  3 |  1 |  7 |

For ORDER BY d DESC, c DESC, b ASC, a ASC , result will be like that

 |  a  |  b |  c |  d | 
 |  10 |  5 |  2 |  7 |
 |  10 |  3 |  1 |  7 |
 |  3  |  5 |  9 |  4 |
 |  3  |  6 |  6 |  4 |
 |  3  |  6 |  2 |  4 |
 |  3  |  5 |  1 |  4 |
 |  5  |  4 |  6 |  3 |
 |  5  |  4 |  6 |  3 |
 |  5  |  5 |  6 |  3 |
 |  5  |  5 |  6 |  3 |
 |  4  |  2 |  5 |  3 |
 |  4  |  2 |  5 |  3 |

Please play the given example with order commands here http://rextester.com/LKBRK29917

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269703

The two are very, very different.

ASC and DESC apply to a specific key. It is not related to the order of the keys.

So, the first ORDER BY orders by column a in ascending order. The rest of the keys are only used when there are ties a.

The second ORDER BY orders by column d in descending order. The rest of the keys are only used when there are ties in d.

Of course, you could find specific data where the two versions are the same. But that would be highly unusual.

Upvotes: 3

jleach
jleach

Reputation: 7792

The order of columns/expressions showing up does matter. It orders by the first one as specified, then orders that set by the next one, then by the next. The result is different than if you were to reverse the order in which the columns are ordered.

Think of each ordered column as a sub-order of the previous ordered column.

(really, you could have tested this in less time than it took to write the question...)

Upvotes: 2

Related Questions