MySQL ORDER BY

ORDER BY

Sort: It only makes sense after the result set is in the Where, group by, have after

Desc (Descending)/asc (ascending)

Sort by field

Sort in descending order of Shop_price

Select Goods_name,cat_id,shop_price from goods where cat_id=4 order by shop_price Desc;

Multiple sort selections, first based on cat_id, then Shop_price

Select Cat_id,shop_price,goods_name from Goods order by cat_id, Shop_price;

Limit limits [POS, optional offset] Quantity

Top 10 out in ascending order

Select Goods_id,goods_name from goods where cat_id=3 ORDER by shop_price ASC limit 10;

Top five of the highest price

Mysql> Select Goods_name, shop_price from goods order BY Shop_price desc limit 0, 5;

Equivalent

Mysql> Select Goods_name, shop_price from goods order BY shop_price DESC LIMIT 5;

+----------------+------------+

| Goods_name | Shop_price |

+----------------+------------+

|    Multi-reach Touch HD | 5999.00 |

|    Nokia N96 | 3700.00 |

|    Nokia N85 | 3010.00 |

|    Testphone | 3000.00 |

|    Amoi T5 | 2878.00 |

+----------------+------------+

The highest-priced three (or third-to fifth-place) starting from the third.

Mysql> Select Goods_name, shop_price from goods order BY shop_price DESC limit 2, 3;

+------------+------------+

| Goods_name | Shop_price |

+------------+------------+

|    Nokia N85 | 3010.00 |

|    Testphone | 3000.00 |

|    Amoi T5 | 2878.00 |

+------------+------------+

Take out the most expensive items

Mysql> Select Goods_name, shop_price from goods order BY shop_price desc LIMIT 1;

+----------------+------------+

| Goods_name | Shop_price |

+----------------+------------+

|    Multi-reach Touch HD | 5999.00 |

+----------------+------------+

Tip: [Judging where] [grouping group_by] [filter having] [sort order BY] [filter limit]

Remove the latest product from each type

Select cat_id, goods_id, Goods_name from (

(select cat_id,goods_id, goods_name from goods order by CAT_ID desc,goods_id desc) as TMP

) Group by cat_id ORDER by cat_id Desc;

Select cat_id,goods_id, goods_name from goods where goods_id in (

Select Max (goods_id) from goods GROUP by cat_id

) Order BY cat_id Desc;

The result of the query can be

Single line can be filtered with = again

Single row multiple rows can be filtered again with in

Multi-row multiple rows can be filtered with from

This article is from the "Code Easy" blog, please be sure to keep this source http://codeyi.blog.51cto.com/11082384/1732465

MySQL ORDER BY