CODEWITHSUNDEEP
mysqlsqlsql-order-by
muzahidbechara
muzahidbechara

Reputation: 253

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?

Upvotes: 2

Views: 689

Answers (2)

Chris Morgan
Chris Morgan

Reputation: 2080

Assuming you understand how MOD works, here's the table showing that the values are actually in the correct order. You could generate this yourself by changing your select statement to be:

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

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

Upvotes: 1

Kenney
Kenney

Reputation: 9093

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 |
+------+------+-------+-------+

Upvotes: 5

Related Questions