muzahidbechara muzahidbechara - 7 months ago 30
SQL Question

MySQL Query ORDER BY with modulus

My SELECT query

SELECT ID, NAME FROM TBL_USER ORDER BY ID % 2, ID % 3


OUTPUT:

ID | NAME
--------|---------
6 | AB
12 | BC
4 | XY
10 | YZ
2 | C1
8 | C2
3 | A3
9 | MN
1 | K2
7 | WQ
13 | OP
5 | HJ
11 | KN


I'm confused about how ORDER BY works with MODULUS(%). How this order generated?

Answer

If you change the query to

SELECT ID, NAME, ID %2, ID %3  FROM TBL_USER ORDER BY ID % 2, ID % 3

to include the values the ORDER BY operates on it becomes clear:

+------+------+-------+-------+
| ID   | NAME | ID %2 | ID %3 |
+------+------+-------+-------+
|    6 | AB   |     0 |     0 |
|   12 | BC   |     0 |     0 |
|    4 | XY   |     0 |     1 |
|   10 | YZ   |     0 |     1 |
|    2 | C1   |     0 |     2 |
|    8 | C2   |     0 |     2 |
|    9 | MN   |     1 |     0 |
|    3 | A3   |     1 |     0 |
|    1 | K2   |     1 |     1 |
|    7 | WQ   |     1 |     1 |
|   13 | OP   |     1 |     1 |
|    5 | HJ   |     1 |     2 |
|   11 | KN   |     1 |     2 |
+------+------+-------+-------+
Comments