[Mysql] ORDER BY sort and retrieve data

Sorting query results is a very common requirement. We can use the ORDER BY clause to achieve the purpose

The ORDER BY clause takes the name of one or more columns and sorts the output accordingly

Typically, the columns used in the ORDER BY clause will be the columns selected for display. However, in fact, this is not necessary. It is completely legal to sort data with non retrieved columns

1. Specify sorting direction

Ascending sort (from A to Z) ASC keyword (default)

Descending sort (from Z to A) DESC keyword

2. Position of order by clause

When giving the ORDER BY clause, you should ensure that it is after the FROM clause. If LIMIT is used, it must be after ORDER BY. Using clauses in the wrong order will result in an error message

Import data

DROP TABLE IF EXISTS employee_info; 
CREATE TABLE employee_info( 
employee_id VARCHAR(8), 
employee_name VARCHAR(8), 
age INT, 
gender VARCHAR(8), 
salary INT 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
employee_info (employee_id,employee_name,age,gender,salary) 
VALUE ('e001','Bob',28,'male',8000) 
,('e002','Alice',25,'female',6000) 
,('e003','David',26,'male',9000) 
,('e004','Kitty',30,'female',11000) 
,('e005','Allen',24,'male',5500);

employee_info table

1. Sort by single field

Case 1: arrange the query results in descending order according to the age column

SELECT * FROM employee_info ORDER BY age DESC;

Result display:

Case 2: sort query results in ascending order by age column

SELECT * FROM employee_info ORDER BY age ASC;

Result display:

It should be noted that if you do not explicitly specify whether the query results are sorted in ascending or descending order, they will be sorted in ascending order by default

SELECT * FROM employee_info ORDER BY age;

The query result is the same as that of case 2

2. Sort by multiple fields

If sorting by multiple fields, first sort by the first field, then sort by the second field for duplicate records in the first field, and so on

In order to sort by multiple columns, just specify the column names and separate them with commas

Note: the DESC/ASC keyword is only applied to the column names directly in front of it. If you want to arrange in descending / ascending order on multiple columns, you must specify the DESC/ASC keyword for each column

Insert two new pieces of data with the following code:

INSERT INTO 
employee_info (employee_id,employee_name,age,gender,salary) 
VALUE ('e006','Ben',28,'male',8000)
,('e007','George',26,'male',10000);

Employee information table after inserting new data

3. Arrange the query results in descending order according to the age column and ascending order according to the salary column

SELECT * FROM employee_info ORDER BY age DESC,salary ASC;

Result display:

First arrange the data in descending order according to the age column. When the data in the age column are the same, then arrange the data in ascending order according to the salary column. It is observed that David and George are the same age, both 26 years old, and David's salary is lower than George's salary, so David is in front of George

3. Combination of order by and LIMIT

Import data

DROP TABLE IF EXISTS student_score; 
CREATE TABLE student_score( 
stu_id VARCHAR(8), 
stu_name VARCHAR(8), 
chinese INT, 
mathematics INT, 
english INT, 
total_score INT 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
student_score (stu_id,stu_name,chinese,mathematics,english,total_score) 
VALUE ('s001','Alice',80,90,75,245) 
,('s002','Bob',75,80,90,245) 
,('s003','Kitty',60,75,65,200) 
,('s004','Ben',80,80,80,240) 
,('s005','Allen',90,90,80,260);

student_ Score (student transcript)

Question: sort according to the total score, and query the information of the top three students with the total score. If their total scores are the same, they will be arranged in descending order according to their math scores (query the information of the top three students with the total score)

SELECT stu_name,total_score 
FROM student_score 
ORDER BY total_score DESC,mathematics DESC 
LIMIT 3;

Result display:

The ORDER BY clause is used for sorting, and LIMIT is used to LIMIT the number of rows returned

If you only want to find out the information of the top 2 students in the total score, you only need to use the following code:

SELECT stu_name,total_score 
FROM student_score 
ORDER BY total_score DESC,mathematics DESC 
LIMIT 2;

Result display:

Note: the above method has a defect. If the total scores of multiple students are tied for the second place, only one record can be found. For example, the total scores of student Bob here are the same as those of Alice. They should be tied for the second place, but only the total scores of Alice can be displayed

Extension: use the combination of ORDER BY and LIMIT to find the highest or lowest value in a column

Tags: MySQL SQL

Posted by jbol on Sun, 24 Apr 2022 19:05:29 +0300