Sorting application scenarios

We have mastered the use of SELECT statement combined with WHERE query conditions to obtain the required data, but in the practical application will also encounter such requirements, such as students sorted from height to low, goods sorted by price, blog articles sorted by the order of publication and so on, so how to solve such requirements?

The use of order by

In SQL, you can use order by to sort a query result set by one or more columns.

SELECT column_name1, column_name2, column_name3 
FROM table_name1,table_name2
ORDER BY  column_name, column_name [ASC|DESC]
Copy the code

TIP:

  1. ASC indicates the ascending order, DESC indicates the descending order.
  2. Columns are sorted in ascending order by default.

For example, arrange the students in the table in ascending order of age and descending order of height:

 select name,age,height from student order by age,height desc;
Copy the code

The use of limit

LIMIT clauses are used in SELECT statements to constrain the number of records to return, and LIMIT is often used for paging. The syntax is as follows:

SELECT  column_name1, column_name2, column_name3 
FROM table_name1,table_name2
LIMIT [offset] row_count;
Copy the code

TIP:

  1. Offset specifies the offset of the first row to be returned. The offset of the first row is 0, not 1;
  2. Row_count specifies the maximum number of rows to return.

Limit paging formula: limit (page-1) * row_count,row_count

For example, get the data from page 100 of the student table, 50 items per page:

# the first100Page offset :(100- 1) * 50
select name,age,sex from student limit 4950.50
Copy the code