MySQL learning footprint record 03-order by, DESC

MySQL learning footprint record 03-order by, DESC 1. order by in ORDER to form a comparison, first list the results without order by sorting.

Mysql> SELECT prod_name FROM products; # influenced by the effect of MySQL re-recycling the bucket, # The results of each query may be different + ---------------- + | prod_name | + -------------- + |. 5 ton andevil | 1 ton andevil | 2 ton andevil | Detonator | Bird seed | Carrots | Fuses | JetPack 1000 | JetPack 2000 | Oil can | safe | Sling | TNT (1 stick) | TNT (5 sticks) | + ---------------- + * sort by order eg: mysql> SELECT prod_name FROM products order by prod_price; + ---------------- + | prod_name | + ---------------- + | TNT (1 stick) | Carrots | Fuses | Sling |. 5 ton andevil | Oil can | 1 ton andevil | TNT (5 sticks) | Bird seed | Detonator | 2 ton andevil | JetPack 1000 | Safe | JetPack 2000 | + ---------------- + * sort by multiple columns (first finish, then, sort B FROM the result.) For example: mysql> SELECT prod_name, prod_id, prod_price FROM products ORDER BY prod_price, prod_name; # sort BY prod_price first, then sort by prod_name + ---------------- + --------- + ------------ + | prod_name | prod_id | prod_price | + ------------------ + --------- + ------------ + | Carrots | FC | 2.50 | TNT (1 stick) | TNT1 | 2.50 | Fuses | FU1 | 3.42 | Sling | SLING | 4.49 |. 5 ton andevil | ANV01 | 5.99 | Oil can | OL1 | 8.99 | 1 ton andevil | ANV02 | 9.99 | Bird seed | FB | 10.00 | TNT (5 sticks) | TNT2 | 10.00 | Detonator | DTNTR | 13.00 | 2 ton andevil | ANV03 | 14.99 | JetPack 1000 | JP1000 | 35.00 | Safe | 50.00 | JetPack 2000 | JP2000 | 55.00 | + ---------------- + --------- + ------------ +

 

2. Specify the sorting direction * the default sorting direction is ASC. the DESC keyword must be used for descending order.

Eg: mysql> SELECT prod_name, prod_id, prod_price FROM products order by prod_price DESC; + ---------------- + --------- + ------------ + | prod_name | prod_id | prod_price | + ------------------ + --------- + ------------ + | JetPack 2000 | JP2000 | 55.00 | Safe | 50.00 | JetPack 1000 | JP1000 | 35.00 | 2 ton andevil | ANV03 | 14.99 | Detonator | DTNTR | 13.00 | TNT (5 sticks) | TNT2 | 10.00 | Bird seed | FB | 10.00 | 1 ton andevil | ANV02 | 9.99 | Oil can | OL1 | 8.99 |. 5 ton andevil | ANV01 | 5.99 | Sling | SLING | 4.49 | Fuses | FU1 | 3.42 | Carrots | FC | 2.50 | TNT (1 stick) | TNT1 | 2.50 | + ---------------- + --------- + ------------ + * sort mysql> SELECT prod_name, prod_id, prod_price FROM products order by prod_price DESC, prod_name in descending ORDER; + ---------------- + --------- + ------------ + | prod_name | prod_id | prod_price | + ------------------ + --------- + ------------ + | JetPack 2000 | JP2000 | 55.00 | Safe | 50.00 | JetPack 1000 | JP1000 | 35.00 | 2 ton andevil | ANV03 | 14.99 | Detonator | DTNTR | 13.00 | Bird seed | FB | 10.00 | TNT (5 sticks) | TNT2 | 10.00 | 1 ton andevil | ANV02 | 9.99 | Oil can | OL1 | 8.99 |. 5 ton andevil | ANV01 | 5.99 | Sling | SLING | 4.49 | Fuses | FU1 | 3.42 | Carrots | FC | 2.50 | TNT (1 stick) | TNT1 | 2.50 | + ---------------- + --------- + ------------ +

 

3. Combination of order by and LIMIT * SQL statements are composed of clauses. Some clauses are mandatory, while others are optional.

  mysql> SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;+------------+| prod_price |+------------+|      55.00 |+------------+