Introduction
MySQL is a well-liked open-source database administration system that’s extensively utilized in net functions. One of many basic duties in working with MySQL is querying knowledge from tables. The ORDER BY clause is a key characteristic of MySQL queries that allows you to type the end result set primarily based on a number of columns. By default, MySQL kinds the end result set in ascending order primarily based on the desired column(s), however you can even type the info in descending order or use features to type the info.
On this information, we’ll take a better have a look at the
ORDER BYclause in MySQL and the way it works. We’ll begin by discussing what theORDER BYclause is and the way it works in MySQL. Then, we’ll cowl the syntax of theORDER BYclause and clarify the completely different elements of the syntax. We’ll additionally focus on the several types of sorting in MySQL and tips on how to type knowledge in ascending and descending order. We’ll discover tips on how to use theORDER BYclause with a number of columns and features, and tips on how to restrict the outcomes of the question. By the top of this information, you will have a stable understanding of tips on how to use theORDER BYclause in MySQL to type and restrict your question outcomes.
What’s the ORDER BY Clause?
The ORDER BY clause is a characteristic of MySQL that allows you to type the end result of a question primarily based on a number of columns. It lets you management the order during which the info is introduced within the question end result.
By default, MySQL kinds the end in ascending order primarily based on the desired column(s). For instance, if in case you have a desk of buyer names, you need to use the ORDER BY clause to type the names alphabetically:
SELECT * FROM prospects
`ORDER BY` identify;
Be aware: Don’t be concerned in case you do not totally perceive the ORDER BY clause but. This part supplies a short overview of what you are able to do with it, and in a while, we’ll dive into the small print of every clause talked about in an effort to grasp this highly effective instrument.
You may as well use the ORDER BY clause to type knowledge in descending order. To do that, you’ll be able to add the DESC key phrase after the column identify:
SELECT * FROM prospects
`ORDER BY` identify DESC;
Along with sorting by a column, you can even use features to type the info. For instance, you need to use the LENGTH perform to type the info by the size of a column:
SELECT * FROM prospects
`ORDER BY` LENGTH(identify);
Now, let’s check out the syntax of the ORDER BY clause in MySQL.
Syntax of ORDER BY Clause
The syntax of the ORDER BY clause in MySQL is as follows:
SELECT column_name(s)
FROM table_name
WHERE situation
`ORDER BY` column_name1, column_name2, ..., column_nameN ASC|DESC;
The ORDER BY clause is used on the finish of the SELECT assertion, after the WHERE clause (if one is used), and earlier than any LIMIT clause (if one is used).
Let’s break down the elements of the syntax:
column_name(s)– Specifies the columns to be chosen within the question end result. You possibly can specify a number of columns separated by commas.table_name– Specifies the identify of the desk from which to retrieve the info.situation– Specifies a situation that should be met for the rows to be included within the end result set. That is an elective part.column_name1, column_name2, ..., column_nameN– Specifies the columns by which to type the end result set. You possibly can specify a number of columns separated by commas. In case you do not specify the sorting order, MySQL will type the end result set in ascendingORDER BYdefault.ASC|DESC– Specifies the sorting order for the columns.ASCstands for ascending order (which is the default), andDESCstands for descending order.
Here is an instance of the ORDER BY clause in motion:
SELECT identify, age, e-mail
FROM prospects
WHERE age > 25
`ORDER BY` identify DESC, age ASC;
This question will return the identify, age, and e-mail columns from the prospects desk for all rows the place the age is larger than 25. The end result set will probably be sorted by the identify column in descending order after which by the age column in ascending order.
To sum all of it up, the syntax of the ORDER BY clause consists of specifying the columns to be chosen, the desk from which to retrieve the info, an elective situation, and the columns by which to type the end result set, together with their sorting order.
Totally different Methods to Kind Knowledge in MySQL
The ORDER BY clause in MySQL lets you type the end result set of a question primarily based on a number of columns. You possibly can type the info in ascending or descending order and use features to type the info. Let’s check out a number of other ways you’ll be able to type knowledge in MySQL.
Sorting in Ascending Order in MySQL
As we have seen earlier, MySQL kinds the end result set in ascending order primarily based on the desired column(s) by default. To type the info in ascending order, you’ll be able to merely specify the column identify(s) within the ORDER BY clause:
SELECT * FROM prospects
`ORDER BY` identify;
This question will return all of the rows from the prospects desk and get them organized by the identify column in ascending order.
Sorting in Descending Order in MySQL
To type the info in descending order, you’ll be able to add the DESC key phrase after the column identify within the ORDER BY clause:
SELECT * FROM prospects
ORDER BY identify DESC;
This question will return all of the rows from the prospects desk and get them organized by the identify column in descending order.
Sorting by A number of Columns
In MySQL, you need to use the ORDER BY clause to type the end result set of a question primarily based on a number of columns. This may be helpful whenever you wish to type the info by a couple of criterion or have numerous matching entries in a column, and want to type by a second criterion, reminiscent of “type alphabetically” and on matching entries, “type by tenure”.
Take a look at our hands-on, sensible information to studying Git, with best-practices, industry-accepted requirements, and included cheat sheet. Cease Googling Git instructions and truly be taught it!
To make use of the ORDER BY clause with a number of columns, merely specify the column names separated by commas within the ORDER BY clause. MySQL will first type the info primarily based on the primary column specified, after which type the info primarily based on the second column if there are any ties:
SELECT * FROM merchandise
ORDER BY class, worth DESC;
This can return all of the rows from the merchandise desk and get them organized by the class column in ascending order. If there are any ties, MySQL will then type the tied rows by the worth column in descending order.
You may as well specify completely different sorting orders for every column. To type the info in ascending order for the primary column and descending order for the second column, you need to use the ASC and DESC key phrases respectively:
SELECT * FROM merchandise
ORDER BY class ASC, worth DESC;
This question will yield us with all of the rows from the merchandise desk and get them organized by the class column in ascending order. If there are any ties, MySQL will then type the tied rows by the worth column in descending order.
Sorting with Features in MySQL
You may as well use features and their outcomes to type knowledge in MySQL. For instance, you need to use the LENGTH perform to type the info by the size of a column:
SELECT * FROM prospects
ORDER BY LENGTH(identify) ASC;
This offers you all of the rows from the prospects desk and get them organized by the size of the identify column in ascending order.
This question will give us all of the rows from the merchandise desk and get them organized by the size of the identify column in descending order. The LENGTH perform is used to find out the size of the identify column.
You may as well use features together with column names within the ORDER BY clause. This may be helpful whenever you wish to type the info by a mixture of a column’s worth and a calculated worth:
SELECT * FROM merchandise
ORDER BY class, worth / amount DESC;
This question will return all of the rows from the merchandise desk and get them organized by the class column in ascending order. If there are any ties, MySQL will then type the tied rows by the results of the worth / amount calculation in descending order.
Be aware: You should use quite a lot of features inside the ORDER BY clause in MySQL, together with:
- Combination features reminiscent of
COUNT,SUM,AVG,MIN, andMAX. - Mathematical features reminiscent of
ABS,CEILING,FLOOR,ROUND, andTRUNCATE. - String features reminiscent of
LENGTH,LEFT,RIGHT,UPPER, andLOWER. - Date and time features reminiscent of
NOW,DATE,TIME,MONTH,YEAR, andDAYOFWEEK.
Limiting Leads to ORDER BY Clause
In MySQL, you need to use the LIMIT clause with the ORDER BY clause to restrict the variety of outcomes returned by a question. This may be helpful whenever you solely wish to show a sure variety of rows or when coping with giant databases.
To make use of the LIMIT clause with the ORDER BY clause, merely specify the variety of rows to return after the ORDER BY clause:
SELECT * FROM merchandise
ORDER BY worth DESC
LIMIT 5;
This can return the highest 5 rows from the merchandise desk sorted by the worth column in descending order.
You may as well use the OFFSET clause with the LIMIT clause to skip a sure variety of rows earlier than returning the end result set:
SELECT * FROM merchandise
ORDER BY worth DESC
LIMIT 5 OFFSET 5;
This offers you 5 rows from the merchandise desk sorted by the worth column in descending order, ranging from the sixth row.
As well as, you can even use variables to set the restrict and offset values dynamically:
SET @restrict = 5;
SET @offset = 5;
SELECT * FROM merchandise
ORDER BY worth DESC
LIMIT @restrict OFFSET @offset;
Conclusion
The ORDER BY clause in MySQL is a strong instrument that lets you type the end result set of a question in a particular order primarily based on a number of columns. By default, the ORDER BY clause kinds the end result set in ascending order, however you can even specify descending ORDER BY utilizing the DESC key phrase.
As well as, you need to use the ORDER BY clause with a number of columns to type the info by a couple of column, and with features to type the info primarily based on the outcomes of a perform. Moreover, you need to use the LIMIT and OFFSET clauses to restrict the variety of outcomes returned by a question.
In case you’re working with databases, then you definitely merely can not afford to disregard the ORDER BY clause in MySQL. It is the boss of sorting knowledge and presenting it in a method that is sensible. And, with the insights you’ve got gained from this information, you are now the boss of the ORDER BY clause!
