MySQL Sort data

One: normal use of ORDER by

1. Introduction

When you query data in a table by using the SELECT statement, the result set is not sorted in any order. To sort the result set, use the ORDER BY clause.

The ORDER by clause allows:

Sort the result set for a single column or multiple columns.
Sorts the result sets of different columns in ascending or descending order.

How to use:

　　　　SELECT column1, column2,...

from tbl 

< Span class= "token punctuation" > ORDER < Span class= "token punctuation" > by column1 [< span class= "token keyword" >asc| Desc][ asc| Desc],.

　　

2. Sort By column

SELECT contactLastname, contactFirstnameFROM customersORDER BY contactLastname DESC, contactFirstname ASC;

　　

In the above query, the ORDER BY clause first contactLastname sorts the result set in descending column order, and then sorts the contactFirstname sorted result set in ascending column order to produce the final result set.

3. Sort by an expression

　　ORDER BYClause also allows you to sort the result set based on an expression

SELECT ordernumber, orderlinenumber, quantityOrdered * priceEachFROM orderdetailsORDER BY ordernumber, orderLineNumber, quantityOrdered * priceEach;

　　

4.order by with custom sorting

　　ORDER BYClause allows FIELD() you to define your own custom sort order for the values in a column by using a function.　　

If you want to sort orders based on values of the following States in the following order:

In Process
On hold
Cancelled
Resolved
Disputed
Shipped

  You can use the FIELD () function to map these values to a list of values and sort them using numbers; see the following query:

select ordernumber, span class= "token keyword" >statusfrom ordersorder by field (status  ' in Process ' ,  ' on Hold '  ' Cancelled ' Span class= "token punctuation",  ' resolved '  ' disputed '  ' Shipped ' "          

Note that there is another status field in the field function.

Two: Order by in natural language order

1. Create a new table

CREATE TABLE IF NOT EXISTS items ( id INT AUTO_INCREMENT PRIMARY KEY, item_no VARCHAR(255) NOT NULL);

　　

2. Inserting data

INSERTinto items(Item_no)VALUES(' 1 ') ( ' 1C ' "  (  ' 10Z ' )  ( ' 2A ' )  ( ' 2 ' "  (  ' 3C ' )  ( ' 20D ' )             

　　

3. Query Results

When we query the selection data and item_no sort it, we get the following results:

SELECT     item_noFROM    itemsORDER BY item_no;

Because this is a natural sort.

4. How to Solve

To overcome this problem, first we item_no divide the columns into two columns: prefix and suffix . prefixThe column stores item_no the number part, and the suffix column stores the letter portion. The data is then sorted according to these columns.

SELECT     item_noFROM    itemsORDER BY CAST(item_no AS UNSIGNED) , item_no;

In this query, you first use type conversions item_no to convert the data to unsigned integers. Second, you use ORDER BY clauses to sort numbers numerically, and then in alphabetical order.

5. The next example

If you delete tables: drop table items

Empty table Now: Drop Tables Items

Add Data:

INSERTinto items(Item_no)VALUES(' A-1 '),(' A-2 '),(' A-3 '),(' A-4 '), (' A-5 '), (' A-10 '), (    ' A-11 '), ( ' A-20 '), (' A-30 ');           

Natural sort:

　　

6. How to Solve

SELECT     item_noFROM    itemsORDER BY LENGTH(item_no) , item_no;

　　

To get the result above, you can use the length function. Note that the length function returns the lengths of the strings. This is done by first item_no sorting the data and then sorting by column values

MySQL Sort data