To make the lexical ordering correspond to the numeric ordering,
mysql>
mysql> CREATE TABLE states
-> (
-> name VARCHAR(30) NOT NULL, # state name
-> abbrev CHAR(2) NOT NULL, # 2-char abbreviation
-> statehood DATE, # date of entry into the Union
-> pop BIGINT, # population as of 4/1990
-> PRIMARY KEY (abbrev)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> insert into states(name, abbrev, statehood, pop)values
-> ("Alabama","AL","1819-12-14",4040587),
-> ("Alaska","AK","1959-01-03",550043),
-> ("Arizona","AZ","1912-02-14",3665228),
-> ("Arkansas","AR","1836-6-15",2350725),
-> ("California","CA","1850-9-9",29760021),
-> ("Colorado","CO","1876-8-1",3294394),
-> ("Connecticut","CT","1788-1-9",3287116),
-> ("Delaware","DE","1787-12-7",666168),
-> ("Florida","FL","1845-3-3",12937926),
-> ("Georgia","GA","1788-1-2",6478216),
-> ("Hawaii","HI","1959-08-21",1108229),
-> ("Idaho","ID","1890-7-3",1006749),
-> ("Illinois","IL","1818-12-3",11430602),
-> ("Indiana","IN","1816-12-11",5544159),
-> ("Iowa","IA","1846-12-28",2776755),
-> ("Kansas","KS","1861-1-29",2477574),
-> ("Kentucky","KY","1792-6-1",3685296),
-> ("Louisiana","LA","1812-4-30",4219973),
-> ("Maine","ME","1820-3-15",1227928),
-> ("Maryland","MD","1788-4-28",4781468),
-> ("Massachusetts","MA","1788-2-6",6016425),
-> ("Michigan","MI","1837-1-26",9295297),
-> ("Minnesota","MN","1858-5-11",4375099);
Query OK, 23 rows affected (0.00 sec)
Records: 23 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT CONCAT(LPAD(pop,8,' '),name) FROM states;
+------------------------------+
| CONCAT(LPAD(pop,8,' '),name) |
+------------------------------+
| 4040587Alabama |
| 550043Alaska |
| 3665228Arizona |
| 2350725Arkansas |
| 29760021California |
| 3294394Colorado |
| 3287116Connecticut |
| 666168Delaware |
| 12937926Florida |
| 6478216Georgia |
| 1108229Hawaii |
| 1006749Idaho |
| 11430602Illinois |
| 5544159Indiana |
| 2776755Iowa |
| 2477574Kansas |
| 3685296Kentucky |
| 4219973Louisiana |
| 1227928Maine |
| 4781468Maryland |
| 6016425Massachusetts |
| 9295297Michigan |
| 4375099Minnesota |
+------------------------------+
23 rows in set (0.00 sec)
mysql>
mysql> drop table states;
Query OK, 0 rows affected (0.00 sec)
Related examples in the same category
1. | Order result wiht ORDER | | |
2. | Use ORDER BY to list | | |
3. | Use two ORDER BY fields | | |
4. | Sorting Rows | | |
5. | Default sort order is ascending | | |
6. | To sort in reverse (descending) order | | |
7. | Sort on multiple columns | | |
8. | Sort columns in different directions | | |
9. | Order decending | | |
10. | Another decendingly | | |
11. | Order BY and Limit | | |
12. | Order row in select clause | | |
13. | Order two columns with different orders | | |
14. | Narrow down data with condition and order it | | |
15. | Simple ORDER by | | |
16. | Sorting Data | | |
17. | Use order by to sort the result | | |
18. | Indicate of ascend | | |
19. | Order by index | | |
20. | ORDER BY RAND | | |
21. | Search string with order | | |
22. | The lack of case sensitivity also applies to relative ordering comparisons: | | |
23. | Refer to the alias in the ORDER BY clause | | |
24. | Columns specified by positions or by aliases can be sorted in either ascending or descending order | | |
25. | Putting the expression directly in the ORDER BY clause: | | |
26. | To achieve the desired output order, display the string, but use the actual numeric size for sorting | | |
27. | Display the composite names, but refer to the constituent values in the ORDER BY clause: | | |
28. | To sort those records in calendar order, use the birthmonth and birthday columns. | | |
29. | To sort by product category, extract the category value and use it in the ORDER BY clause | | |
30. | To use the substrings for sorting, use the appropriate expressions in the ORDER BY clause. | | |
31. | Sorting Hostnames in Domain Order | | |
32. | Sorting Dotted-Quad IP Values in Numeric Order | | |
33. | Floating Specific Values to the Head or Tail of the Sort Order | | |
34. | Sorting in User-Defined Orders | | |
35. | Sort the column by the order in which colors occur in the rainbow. | | |
36. | Controlling Summary Display Order | | |
37. | Sort drivers according to who drove the most days or miles, add the appropriate ORDER BY clause | | |
38. | Order by sum result | | |
39. | To sort the result set as a whole, add an ORDER BY clause after the final SELECT statement. | | |
40. | Enclose a given SELECT (including its ORDER BY clause) within parentheses | | |
41. | The expressions display state names in lexical order within each row | | |
42. | Delete from the Orders table any order for the book title Where I'm Calling From. | | |
43. | SELECT statement includes an ORDER BY clause that sorts the result set according to two columns | | |
44. | Determine how many books have been ordered for authors who have more than one book listed in the Books table. | | |
45. | The Order in Which MySQL Processes Conditions | | |
46. | Return the third, fourth, and fifth records sorted in descending order on the commission field? | | |
47. | Explicit evaluation order | | |
48. | Display order number, quantity, item name, vendor and total order value of order number 2805 | | |
49. | Display all products - including those with no orders | | |
50. | Get the order number and number of items ordered where the color is not Pink and the number of items ordered i | | |
51. | Get players whose combination of name and initials comes before player 6 in alphabetical order. | | |
52. | Order by char type | | |
53. | order by sub string | | |
54. | Order by calculated value | | |
55. | Order by alias name | | |
56. | Order by one column descending and another one ascending | | |
57. | Order then choose the first rows | | |
58. | Refer to Sort Columns | | |
59. | Sorting Expression Results | | |
60. | Sort the results using the underlying column values rather than the displayed composite values | | |
61. | Convert the output column and sort that-but doing so affects the displayed values, possibly in an undesirable | | |
62. | To sort by country code, use the rightmost two characters of the id values | | |
63. | Sorting hostname values correctly in right-to-left fashion: | | |
64. | Sort by position | | |
65. | Sort direction | | |
66. | display all data in "hers" and "his" sorted by id | | |
67. | Sorting Subsets of a Table | | |
68. | Displaying One Set of Values While Sorting by Another | | |