MySQL learning footprint records 03--ORDERBY, DESC_MySQL

MySQL learning footprint records 03--ORDERBY, DESC bitsCN.com

MySQL learning footprint record 03-order by, DESC

1. ORDER

In ORDER to form a comparison, we will first list the results without 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 required, while others are optional.

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

BitsCN.com