How to sorting query results

最新推荐文章于 2024-07-21 19:06:05 发布

DB架构

最新推荐文章于 2024-07-21 19:06:05 发布

阅读量225

点赞数

分类专栏：深入mysql的世界文章标签： mysql

本文链接：https://blog.csdn.net/u011868279/article/details/123650933

版权

深入mysql的世界专栏收录该内容

40 篇文章 0 订阅

订阅专栏

You can sort rows of a query result several ways:

Using a single column, a combination of columns,or even parts of columns or expression results.
Using ascending or descending order
Using case-sensitive or case-insensitive string comparsons
Using temporal ordering

mysql> DROP TABLE IF EXISTS driver_log;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE driver_log
-> (
-> rec_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL,
-> trav_date DATE NOT NULL,
-> miles INT NOT NULL,
-> PRIMARY KEY (rec_id)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO driver_log (name,trav_date,miles)
-> VALUES
-> ('Ben','2014-07-30',152),
-> ('Suzi','2014-07-29',391),
-> ('Henry','2014-07-29',300),
-> ('Henry','2014-07-27',96),
-> ('Ben','2014-07-29',131),
-> ('Henry','2014-07-26',115),
-> ('Suzi','2014-08-02',502),
-> ('Henry','2014-08-01',197),
-> ('Ben','2014-08-02',79),
-> ('Henry','2014-07-30',203)
-> ;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM driver_log;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 1 | Ben | 2014-07-30 | 152 |
| 2 | Suzi | 2014-07-29 | 391 |
| 3 | Henry | 2014-07-29 | 300 |
| 4 | Henry | 2014-07-27 | 96 |
| 5 | Ben | 2014-07-29 | 131 |
| 6 | Henry | 2014-07-26 | 115 |
| 7 | Suzi | 2014-08-02 | 502 |
| 8 | Henry | 2014-08-01 | 197 |
| 9 | Ben | 2014-08-02 | 79 |
| 10 | Henry | 2014-07-30 | 203 |
+--------+-------+------------+-------+
10 rows in set (0.00 sec)

mysql> DROP TABLE IF EXISTS mail;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE mail
-> (
-> t DATETIME, # when message was sent
-> srcuser VARCHAR(8), # sender (source user and host)
-> srchost VARCHAR(20),
-> dstuser VARCHAR(8), # recipient (destination user and host)
-> dsthost VARCHAR(20),
-> size BIGINT, # message size in bytes
-> INDEX (t)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO mail (t,srchost,srcuser,dsthost,dstuser,size)
-> VALUES
-> ('2014-05-11 10:15:08','saturn','barb','mars','tricia',58274),
-> ('2014-05-12 12:48:13','mars','tricia','venus','gene',194925),
-> ('2014-05-12 15:02:49','mars','phil','saturn','phil',1048),
-> ('2014-05-12 18:59:18','saturn','barb','venus','tricia',271),
-> ('2014-05-14 09:31:37','venus','gene','mars','barb',2291),
-> ('2014-05-14 11:52:17','mars','phil','saturn','tricia',5781),
-> ('2014-05-14 14:42:21','venus','barb','venus','barb',98151),
-> ('2014-05-14 17:03:01','saturn','tricia','venus','phil',2394482),
-> ('2014-05-15 07:17:48','mars','gene','saturn','gene',3824),
-> ('2014-05-15 08:50:57','venus','phil','venus','phil',978),
-> ('2014-05-15 10:25:52','mars','gene','saturn','tricia',998532),
-> ('2014-05-15 17:35:31','saturn','gene','mars','gene',3856),
-> ('2014-05-16 09:00:28','venus','gene','mars','barb',613),
-> ('2014-05-16 23:04:19','venus','phil','venus','barb',10294),
-> ('2014-05-19 12:49:23','mars','phil','saturn','tricia',873),
-> ('2014-05-19 22:21:51','saturn','gene','venus','gene',23992)
-> ;
Query OK, 16 rows affected (0.01 sec)
Records: 16 Duplicates: 0 Warnings: 0

1. How to order by to sort query results

Problem

Row in a query result don't appear in the order you want.

Solution

Add an ORDER BY clause to the query to sort its result.

mysql> select * from driver_log order by name;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 1 | Ben | 2014-07-30 | 152 |
| 5 | Ben | 2014-07-29 | 131 |
| 9 | Ben | 2014-08-02 | 79 |
| 3 | Henry | 2014-07-29 | 300 |
| 4 | Henry | 2014-07-27 | 96 |
| 6 | Henry | 2014-07-26 | 115 |
| 8 | Henry | 2014-08-01 | 197 |
| 10 | Henry | 2014-07-30 | 203 |
| 2 | Suzi | 2014-07-29 | 391 |
| 7 | Suzi | 2014-08-02 | 502 |
+--------+-------+------------+-------+
10 rows in set (0.01 sec)

mysql>

mysql> select * from driver_log order by name,trav_date;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 5 | Ben | 2014-07-29 | 131 |
| 1 | Ben | 2014-07-30 | 152 |
| 9 | Ben | 2014-08-02 | 79 |
| 6 | Henry | 2014-07-26 | 115 |
| 4 | Henry | 2014-07-27 | 96 |
| 3 | Henry | 2014-07-29 | 300 |
| 10 | Henry | 2014-07-30 | 203 |
| 8 | Henry | 2014-08-01 | 197 |
| 2 | Suzi | 2014-07-29 | 391 |
| 7 | Suzi | 2014-08-02 | 502 |
+--------+-------+------------+-------+
10 rows in set (0.01 sec)

mysql>

mysql> select name,trav_date,miles as distance from driver_log
-> order by distance;
+-------+------------+----------+
| name | trav_date | distance |
+-------+------------+----------+
| Ben | 2014-08-02 | 79 |
| Henry | 2014-07-27 | 96 |
| Henry | 2014-07-26 | 115 |
| Ben | 2014-07-29 | 131 |
| Ben | 2014-07-30 | 152 |
| Henry | 2014-08-01 | 197 |
| Henry | 2014-07-30 | 203 |
| Henry | 2014-07-29 | 300 |
| Suzi | 2014-07-29 | 391 |
| Suzi | 2014-08-02 | 502 |
+-------+------------+----------+
10 rows in set (0.00 sec)

mysql>

2.How to using expressions for sorting

Problem

You want to sort a query result based on values calculated from a column rather than the values actually stored in the column.

Solution

Put the expression that calculates the values in the order by clause.

The +1023 in the FLOOR() expression groups size values to the nearest upper boundary of the 10240-byte categories.

mysql> select t,srcuser,FLOOR((size+1023)/1024)
-> from mail where size > 50000
-> order by floor ((size+1023)/1024);
+---------------------+---------+-------------------------+
| t | srcuser | FLOOR((size+1023)/1024) |
+---------------------+---------+-------------------------+
| 2014-05-11 10:15:08 | barb | 57 |
| 2014-05-14 14:42:21 | barb | 96 |
| 2014-05-12 12:48:13 | tricia | 191 |
| 2014-05-15 10:25:52 | gene | 976 |
| 2014-05-14 17:03:01 | tricia | 2339 |
+---------------------+---------+-------------------------+
5 rows in set (0.00 sec)

mysql> select t,srcuser,floor((size+1023)/1024) as kilobytes
-> from mail where size > 50000
-> order by kilobytes;
+---------------------+---------+-----------+
| t | srcuser | kilobytes |
+---------------------+---------+-----------+
| 2014-05-11 10:15:08 | barb | 57 |
| 2014-05-14 14:42:21 | barb | 96 |
| 2014-05-12 12:48:13 | tricia | 191 |
| 2014-05-15 10:25:52 | gene | 976 |
| 2014-05-14 17:03:01 | tricia | 2339 |
+---------------------+---------+-----------+
5 rows in set (0.00 sec)

mysql>

3.Displaying One Set of Values While Sorting by Another

Problem:

You want to sort a result set using values that don't appear in the output column list.

Solution:

That's not a problem. The order by clause can refer to columns you don't display.

mysql> select t,srcuser,
-> concat(floor((size+1023)/1024),'k') as size_in_k
-> from mail where size > 50000
-> order by size_in_k;
+---------------------+---------+-----------+
| t | srcuser | size_in_k |
+---------------------+---------+-----------+
| 2014-05-12 12:48:13 | tricia | 191k |
| 2014-05-14 17:03:01 | tricia | 2339k |
| 2014-05-11 10:15:08 | barb | 57k |
| 2014-05-14 14:42:21 | barb | 96k |
| 2014-05-15 10:25:52 | gene | 976k |
+---------------------+---------+-----------+
5 rows in set (0.00 sec)

mysql> select t,srcuser,
-> concat(floor((size+1023)/1024),'K') as size_in_k
-> from mail where size > 50000
-> order by size;
+---------------------+---------+-----------+
| t | srcuser | size_in_k |
+---------------------+---------+-----------+
| 2014-05-11 10:15:08 | barb | 57K |
| 2014-05-14 14:42:21 | barb | 96K |
| 2014-05-12 12:48:13 | tricia | 191K |
| 2014-05-15 10:25:52 | gene | 976K |
| 2014-05-14 17:03:01 | tricia | 2339K |
+---------------------+---------+-----------+
5 rows in set (0.00 sec)

mysql> DROP TABLE IF EXISTS roster;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE roster
-> (
-> name CHAR(30), # player name
-> jersey_num CHAR(3) # jersey number
-> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE roster
-> (
-> name CHAR(30), # player name
-> jersey_num CHAR(3) # jersey number
-> );
ERROR 1050 (42S01): Table 'roster' already exists
mysql> INSERT INTO roster (name, jersey_num)
-> VALUES
-> ('Lynne','29'),
-> ('Ella','0'),
-> ('Elizabeth','100'),
-> ('Nancy','00'),
-> ('Jean','8'),
-> ('Sherry','47')
-> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM roster;
+-----------+------------+
| name | jersey_num |
+-----------+------------+
| Lynne | 29 |
| Ella | 0 |
| Elizabeth | 100 |
| Nancy | 00 |
| Jean | 8 |
| Sherry | 47 |
+-----------+------------+
6 rows in set (0.00 sec)

mysql> select name,jersey_num from roster;
+-----------+------------+
| name | jersey_num |
+-----------+------------+
| Lynne | 29 |
| Ella | 0 |
| Elizabeth | 100 |
| Nancy | 00 |
| Jean | 8 |
| Sherry | 47 |
+-----------+------------+
6 rows in set (0.00 sec)

mysql> select name,jersey_num from roster order by jersey_num;
+-----------+------------+
| name | jersey_num |
+-----------+------------+
| Ella | 0 |
| Nancy | 00 |
| Elizabeth | 100 |
| Lynne | 29 |
| Sherry | 47 |
| Jean | 8 |
+-----------+------------+
6 rows in set (0.00 sec)

mysql> select name,jersey_num from roster order by jersey_num + 0;
+-----------+------------+
| name | jersey_num |
+-----------+------------+
| Ella | 0 |
| Nancy | 00 |
| Jean | 8 |
| Lynne | 29 |
| Sherry | 47 |
| Elizabeth | 100 |
+-----------+------------+
6 rows in set (0.00 sec)

mysql> select t,concat(srcuser,'@',srchost) as sender,size
-> from mail where size > 50000
-> order by srchost,srcuser;
+---------------------+---------------+---------+
| t | sender | size |
+---------------------+---------------+---------+
| 2014-05-15 10:25:52 | gene@mars | 998532 |
| 2014-05-12 12:48:13 | tricia@mars | 194925 |
| 2014-05-11 10:15:08 | barb@saturn | 58274 |
| 2014-05-14 17:03:01 | tricia@saturn | 2394482 |
| 2014-05-14 14:42:21 | barb@venus | 98151 |
+---------------------+---------------+---------+
5 rows in set (0.00 sec)

mysql> DROP TABLE IF EXISTS name;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE name
-> (
-> last_name CHAR(20),
-> first_name CHAR(20)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO name (first_name,last_name) VALUES('Kevin','Brown');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO name (first_name,last_name) VALUES('Vida','Blue');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO name (first_name,last_name) VALUES('Pete','Gray');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO name (first_name,last_name) VALUES('Devon','White');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO name (first_name,last_name) VALUES('Rondell','White');
Query OK, 1 row affected (0.00 sec)

mysql>

4.How to controling case sensitivity of string sorts?

mysql>
mysql> DROP TABLE IF EXISTS str_val;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE str_val
-> (
-> ci_str CHAR(3) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
-> cs_str CHAR(3) CHARACTER SET latin1 COLLATE latin1_general_cs,
-> bin_str BINARY(3)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO str_val (ci_str,cs_str,bin_str) VALUES
-> ('AAA','AAA','AAA'),
-> ('aaa','aaa','aaa'),
-> ('bbb','bbb','bbb'),
-> ('BBB','BBB','BBB');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM str_val;
+--------+--------+---------+
| ci_str | cs_str | bin_str |
+--------+--------+---------+
| AAA | AAA | AAA |
| aaa | aaa | aaa |
| bbb | bbb | bbb |
| BBB | BBB | BBB |
+--------+--------+---------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM str_val ORDER BY ci_str;
+--------+--------+---------+
| ci_str | cs_str | bin_str |
+--------+--------+---------+
| AAA | AAA | AAA |
| aaa | aaa | aaa |
| bbb | bbb | bbb |
| BBB | BBB | BBB |
+--------+--------+---------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM str_val ORDER BY cs_str;
+--------+--------+---------+
| ci_str | cs_str | bin_str |
+--------+--------+---------+
| AAA | AAA | AAA |
| aaa | aaa | aaa |
| BBB | BBB | BBB |
| bbb | bbb | bbb |
+--------+--------+---------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM str_val ORDER BY bin_str;
+--------+--------+---------+
| ci_str | cs_str | bin_str |
+--------+--------+---------+
| AAA | AAA | AAA |
| BBB | BBB | BBB |
| aaa | aaa | aaa |
| bbb | bbb | bbb |
+--------+--------+---------+
4 rows in set (0.00 sec)

mysql>

mysql> select ci_str from str_val
-> order by ci_str collate latin1_general_cs;
+--------+
| ci_str |
+--------+
| AAA |
| aaa |
| BBB |
| bbb |
+--------+
4 rows in set (0.00 sec)

mysql> select cs_str from str_val
-> order by cs_str collate latin1_swedish_ci;
+--------+
| cs_str |
+--------+
| AAA |
| aaa |
| bbb |
| BBB |
+--------+
4 rows in set (0.00 sec)

mysql> select cs_str from str_val
-> order by upper(cs_str);
+--------+
| cs_str |
+--------+
| AAA |
| aaa |
| bbb |
| BBB |
+--------+
4 rows in set (0.00 sec)

mysql> select bin_str from str_val
-> order by convert(bin_str using latin1) collate latin1_swedish_ci;
+---------+
| bin_str |
+---------+
| AAA |
| aaa |
| bbb |
| BBB |
+---------+
4 rows in set (0.00 sec)

mysql>

5.How to Date-Based Sorting

Problem:

Sort rows in temporal order.

Solution:

sort using a date or time column.

Sorting by time of day

mysql>

Sorting by calendar day

mysql> DROP TABLE IF EXISTS occasion;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE occasion
-> (
-> date DATE,
-> description VARCHAR(255)
-> )
-> ;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO occasion (date,description)
-> VALUES
-> ('1789-07-04','US Independence Day'),
-> ('1776-07-14','Bastille Day'),
-> ('1957-10-04','Sputnik launch date'),
-> ('1919-06-28','Signing of the Treaty of Versailles'),
-> ('1732-02-22','George Washington\'s birthday'),
-> ('1989-11-09','Opening of the Berlin Wall'),
-> ('1944-06-06','D-Day at Normandy Beaches'),
-> ('1215-06-15','Signing of the Magna Carta'),
-> ('1809-02-12','Abraham Lincoln\'s birthday')
-> ;
Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM occasion;
+------------+-------------------------------------+
| date | description |
+------------+-------------------------------------+
| 1789-07-04 | US Independence Day |
| 1776-07-14 | Bastille Day |
| 1957-10-04 | Sputnik launch date |
| 1919-06-28 | Signing of the Treaty of Versailles |
| 1732-02-22 | George Washington's birthday |
| 1989-11-09 | Opening of the Berlin Wall |
| 1944-06-06 | D-Day at Normandy Beaches |
| 1215-06-15 | Signing of the Magna Carta |
| 1809-02-12 | Abraham Lincoln's birthday |
+------------+-------------------------------------+
9 rows in set (0.00 sec)

mysql> select date,description from occasion order by date;
+------------+-------------------------------------+
| date | description |
+------------+-------------------------------------+
| 1215-06-15 | Signing of the Magna Carta |
| 1732-02-22 | George Washington's birthday |
| 1776-07-14 | Bastille Day |
| 1789-07-04 | US Independence Day |
| 1809-02-12 | Abraham Lincoln's birthday |
| 1919-06-28 | Signing of the Treaty of Versailles |
| 1944-06-06 | D-Day at Normandy Beaches |
| 1957-10-04 | Sputnik launch date |
| 1989-11-09 | Opening of the Berlin Wall |
+------------+-------------------------------------+
9 rows in set (0.00 sec)

mysql> select date,description from occasion
-> order by month(date),dayofmonth(date);
+------------+-------------------------------------+
| date | description |
+------------+-------------------------------------+
| 1809-02-12 | Abraham Lincoln's birthday |
| 1732-02-22 | George Washington's birthday |
| 1944-06-06 | D-Day at Normandy Beaches |
| 1215-06-15 | Signing of the Magna Carta |
| 1919-06-28 | Signing of the Treaty of Versailles |
| 1789-07-04 | US Independence Day |
| 1776-07-14 | Bastille Day |
| 1957-10-04 | Sputnik launch date |
| 1989-11-09 | Opening of the Berlin Wall |
+------------+-------------------------------------+
9 rows in set (0.00 sec)

mysql> select dayofyear('1996-02-29'),dayofyear('1997-03-01');
+-------------------------+-------------------------+
| dayofyear('1996-02-29') | dayofyear('1997-03-01') |
+-------------------------+-------------------------+
| 60 | 60 |
+-------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

Sorting by day of week

mysql>

6.How to sorting by substrings of column values

Problem

You want to sort a set of values using one or more substrings of each value.

Solution

Extract the pieces you want and sort tem separately.

7. How to sorting by fixed -length substrings

Problem

You want to sort using parts of a column that occur at a given position within the column.

Solution

Pull out the parts you need with LEFT(),MID(), or RIGHT(),and sort them.

mysql> DROP TABLE IF EXISTS housewares;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE housewares
-> (
-> id VARCHAR(20),
-> description VARCHAR(255)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO housewares (id,description)
-> VALUES
-> ('DIN40672US', 'dining table'),
-> ('KIT00372UK', 'garbage disposal'),
-> ('KIT01729JP', 'microwave oven'),
-> ('BED00038SG', 'bedside lamp'),
-> ('BTH00485US', 'shower stall'),
-> ('BTH00415JP', 'lavatory')
-> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> DROP TABLE IF EXISTS housewares2;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE housewares2
-> (
-> category VARCHAR(3) NOT NULL,
-> serial INT(5) UNSIGNED ZEROFILL NOT NULL,
-> country VARCHAR(2) NOT NULL,
-> description VARCHAR(255),
-> PRIMARY KEY (category, country, serial)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO housewares2 (category,serial,country,description)
-> VALUES
-> ('DIN', 40672, 'US', 'dining table'),
-> ('KIT', 372, 'UK', 'garbage disposal'),
-> ('KIT', 1729, 'JP', 'microwave oven'),
-> ('BED', 38, 'SG', 'bedside lamp'),
-> ('BTH', 485, 'US', 'shower stall'),
-> ('BTH', 415, 'JP', 'lavatory')
-> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM housewares2;
+----------+--------+---------+------------------+
| category | serial | country | description |
+----------+--------+---------+------------------+
| BED | 00038 | SG | bedside lamp |
| BTH | 00415 | JP | lavatory |
| BTH | 00485 | US | shower stall |
| DIN | 40672 | US | dining table |
| KIT | 01729 | JP | microwave oven |
| KIT | 00372 | UK | garbage disposal |
+----------+--------+---------+------------------+
6 rows in set (0.00 sec)

mysql> DROP TABLE IF EXISTS housewares3;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE housewares3
-> (
-> id VARCHAR(20),
-> description VARCHAR(255)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO housewares3 (id,description)
-> VALUES
-> ('DIN40672US', 'dining table'),
-> ('KIT372UK', 'garbage disposal'),
-> ('KIT1729JP', 'microwave oven'),
-> ('BED38SG', 'bedside lamp'),
-> ('BTH485US', 'shower stall'),
-> ('BTH415JP', 'lavatory')
-> ;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> DROP TABLE IF EXISTS housewares4;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE housewares4
-> (
-> id VARCHAR(20),
-> description VARCHAR(255)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO housewares4 (id,description)
-> VALUES
-> ('13-478-92-2', 'dining table'),
-> ('873-48-649-63', 'garbage disposal'),
-> ('8-4-2-1', 'microwave oven'),
-> ('97-681-37-66', 'bedside lamp'),
-> ('27-48-534-2', 'shower stall'),
-> ('5764-56-89-72', 'lavatory')
-> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql>
mysql> SELECT * FROM housewares4;
+---------------+------------------+
| id | description |
+---------------+------------------+
| 13-478-92-2 | dining table |
| 873-48-649-63 | garbage disposal |
| 8-4-2-1 | microwave oven |
| 97-681-37-66 | bedside lamp |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
+---------------+------------------+
6 rows in set (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS hw_category;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE hw_category
-> (
-> abbrev VARCHAR(3),
-> name VARCHAR(20)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO hw_category (abbrev,name)
-> VALUES
-> ('DIN', 'dining'),
-> ('KIT', 'kitchen'),
-> ('BTH', 'bathroom'),
-> ('BED', 'bedroom')
-> ;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql>

mysql> select id,
-> left(id,3) as category,
-> mid(id,4,5) as serial,
-> right(id,2) as country
-> from housewares;
+------------+----------+--------+---------+
| id | category | serial | country |
+------------+----------+--------+---------+
| DIN40672US | DIN | 40672 | US |
| KIT00372UK | KIT | 00372 | UK |
| KIT01729JP | KIT | 01729 | JP |
| BED00038SG | BED | 00038 | SG |
| BTH00485US | BTH | 00485 | US |
| BTH00415JP | BTH | 00415 | JP |
+------------+----------+--------+---------+
6 rows in set (0.00 sec)

mysql> select category,serial,country,
-> concat(category,serial,country) as id
-> from housewares2 order by category,country,serial;
+----------+--------+---------+------------+
| category | serial | country | id |
+----------+--------+---------+------------+
| BED | 00038 | SG | BED00038SG |
| BTH | 00415 | JP | BTH00415JP |
| BTH | 00485 | US | BTH00485US |
| DIN | 40672 | US | DIN40672US |
| KIT | 01729 | JP | KIT01729JP |
| KIT | 00372 | UK | KIT00372UK |
+----------+--------+---------+------------+
6 rows in set (0.00 sec)

mysql>

8.How to sorting by variable-length substrings

Problem

You want to sort using parts of a column that do not occur at a given position within the column.

Solution

Determine how to identify the parts you need so that you can extract them.

mysql> select id,left(substring(id,4),char_length(substring(id,4)-2))
-> from housewares3;
+------------+------------------------------------------------------+
| id | left(substring(id,4),char_length(substring(id,4)-2)) |
+------------+------------------------------------------------------+
| DIN40672US | 40672 |
| KIT372UK | 372 |
| KIT1729JP | 1729 |
| BED38SG | 38 |
| BTH485US | 485 |
| BTH415JP | 415 |
+------------+------------------------------------------------------+
6 rows in set (0.00 sec)

mysql> select id,substring(id,4),substring(id,4,char_length(id)-5)
-> from housewares3;
+------------+-----------------+-----------------------------------+
| id | substring(id,4) | substring(id,4,char_length(id)-5) |
+------------+-----------------+-----------------------------------+
| DIN40672US | 40672US | 40672 |
| KIT372UK | 372UK | 372 |
| KIT1729JP | 1729JP | 1729 |
| BED38SG | 38SG | 38 |
| BTH485US | 485US | 485 |
| BTH415JP | 415JP | 415 |
+------------+-----------------+-----------------------------------+
6 rows in set (0.00 sec)

mysql> select * from housewares4;
+---------------+------------------+
| id | description |
+---------------+------------------+
| 13-478-92-2 | dining table |
| 873-48-649-63 | garbage disposal |
| 8-4-2-1 | microwave oven |
| 97-681-37-66 | bedside lamp |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
+---------------+------------------+
6 rows in set (0.00 sec)

mysql> select
-> id,
-> substring_index(substring_index(id,'-',2),'-',-1) as segment2,
-> substring_index(substring_index(id,'-',4),'-',-1) as segment4
-> from housewares4;
+---------------+----------+----------+
| id | segment2 | segment4 |
+---------------+----------+----------+
| 13-478-92-2 | 478 | 2 |
| 873-48-649-63 | 48 | 63 |
| 8-4-2-1 | 4 | 1 |
| 97-681-37-66 | 681 | 66 |
| 27-48-534-2 | 48 | 2 |
| 5764-56-89-72 | 56 | 72 |
+---------------+----------+----------+
6 rows in set (0.00 sec)

mysql> select * from housewares4
-> order by substring_index(substring_index(id,'-',2),'-',-1);
+---------------+------------------+
| id | description |
+---------------+------------------+
| 8-4-2-1 | microwave oven |
| 13-478-92-2 | dining table |
| 873-48-649-63 | garbage disposal |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
| 97-681-37-66 | bedside lamp |
+---------------+------------------+
6 rows in set (0.00 sec)

mysql> select * from housewares4 order by substring_index(substring_index(id,'-',2),'-',-1)+0;
+---------------+------------------+
| id | description |
+---------------+------------------+
| 8-4-2-1 | microwave oven |
| 873-48-649-63 | garbage disposal |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
| 13-478-92-2 | dining table |
| 97-681-37-66 | bedside lamp |
+---------------+------------------+
6 rows in set (0.00 sec)

mysql>

9.How to sorting hostnames in domain order

Problem

You want to sort hostnames in domain order.with the rightmost parts of the names more significant than the leftmost parts.

Solution

Break apart the names, and sort the pieces from right to left.

mysql>
mysql> DROP TABLE IF EXISTS hostname;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE hostname
-> (
-> name VARCHAR(64)
-> );

Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO hostname (name)
-> VALUES
-> ('svn.php.net'),
-> ('dbi.perl.org'),
-> ('lists.mysql.com'),
-> ('mysql.com'),
-> ('jakarta.apache.org'),
-> ('www.kitebird.com')
-> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql>

10.How to sorting dotted-quad IP values in number order

Problem

sort in numeric order strings that represent IP numbers.

Solution

Break apart the strings,and sort the pieces numerically,or just use INET_ATON(),Or consider storing the values as numbers instead.

mysql>
mysql> DROP TABLE IF EXISTS hostip;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE hostip
-> (
-> ip VARCHAR(64)
-> );

Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO hostip (ip)
-> VALUES
-> ('127.0.0.1'),
-> ('192.168.0.2'),
-> ('192.168.0.10'),
-> ('192.168.1.2'),
-> ('192.168.1.10'),
-> ('255.255.255.255'),
-> ('21.0.0.1')
-> ;
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql>
mysql> SELECT * FROM hostip ORDER BY ip;
+-----------------+
| ip |
+-----------------+
| 127.0.0.1 |
| 192.168.0.10 |
| 192.168.0.2 |
| 192.168.1.10 |
| 192.168.1.2 |
| 21.0.0.1 |
| 255.255.255.255 |
+-----------------+
7 rows in set (0.00 sec)

mysql> select ip from hostip order by ip;
+-----------------+
| ip |
+-----------------+
| 127.0.0.1 |
| 192.168.0.10 |
| 192.168.0.2 |
| 192.168.1.10 |
| 192.168.1.2 |
| 21.0.0.1 |
| 255.255.255.255 |
+-----------------+
7 rows in set (0.00 sec)

mysql> select ip from hostip
-> order by
-> substring_index(ip,'.',1)+0,
-> substring_index(substring_index(ip,'.',-3),'.',1)+0,
-> substring_index(substring_index(ip,'.',-2),'.',1)+0,
-> substring_index(ip,'.',-1)+0;
+-----------------+
| ip |
+-----------------+
| 21.0.0.1 |
| 127.0.0.1 |
| 192.168.0.2 |
| 192.168.0.10 |
| 192.168.1.2 |
| 192.168.1.10 |
| 255.255.255.255 |
+-----------------+
7 rows in set (0.00 sec)

mysql> select ip from hostip order by inet_aton(ip);
+-----------------+
| ip |
+-----------------+
| 21.0.0.1 |
| 127.0.0.1 |
| 192.168.0.2 |
| 192.168.0.10 |
| 192.168.1.2 |
| 192.168.1.10 |
| 255.255.255.255 |
+-----------------+
7 rows in set (0.00 sec)

mysql> select ip,ip+0 from hostip;
+-----------------+---------+
| ip | ip+0 |
+-----------------+---------+
| 127.0.0.1 | 127 |
| 192.168.0.2 | 192.168 |
| 192.168.0.10 | 192.168 |
| 192.168.1.2 | 192.168 |
| 192.168.1.10 | 192.168 |
| 255.255.255.255 | 255.255 |
| 21.0.0.1 | 21 |
+-----------------+---------+
7 rows in set, 7 warnings (0.00 sec)

mysql>