I was writing a MySQL database query and ran into an interesting challenge where my result set needed to be ordered in a predefined order to meet business logic requirements. Typically the ORDER BY clause will sort using numeric or alphabetical order. This is pretty limiting if you want to define an enumeration of values that should provide the sort order. A simple example of this is using ORDER BY on a column that stores numbers as strings, like ‘one’, ‘two’, ‘three’, ‘four’, etc. Using ORDER BY on this field will yield a result set ordered by those strings in alphabetical order, not the integer values that they represent.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SELECT * FROM my_table
ORDER BY my_number
+----------+------------+
| my_value | my_number |
+----------+------------+
| 3 | Four |
| 663 | Four |
| 18 | One |
| 14 | One |
| 225 | Three |
| 554 | Three |
| 554 | Two |
| 34 | Two |
+----------+------------+
|
The way to order this using the order ‘one’, ‘two’, ‘three’, ‘four’ is to use the
MySQL Field() function. Using Field() it is possible to define the order which will return the index of the string position that matches string. The MySQL documentation is incomplete with using Field() in conjunction with ORDER BY, but this example illustrates the syntax.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SELECT * FROM my_table
ORDER BY FIELD(my_number,'One','Two','Three','Four') ASC
+----------+------------+
| my_value | my_number |
+----------+------------+
| 18 | One |
| 14 | One |
| 554 | Two |
| 34 | Two |
| 225 | Three |
| 554 | Three |
| 3 | Four |
| 663 | Four |
+----------+------------+
|
The strings used to ORDER BY do not need to represent numbers. In my challenge I needed to order the results by a status column that contained a number of different strings and business rules dictated the order of importance for the MySQL query in question.
Posts
![IVRAMANA](https://draft.blogger.com/img/profile_blank_gray.jpg" title="IVRAMANA)
0 comments:
Post a Comment