At the point when you utilize the SELECT assertion to question information from a table, the request for lines in the outcome set is undefined. To sort the lines in the outcome set, you add the Request BY provision to the SELECT assertion.
The MySQL Request BY Watchword
The Request BY watchword is utilized to sort the outcome set in climbing or sliding request.
The Request BY catchphrase sorts the records in climbing request as a matter of course. To sort the records in dropping request, utilize the DESC catchphrase.
Request BY Language structure
SELECT column1, column2, ...
FROM table_name
Request BY column1, column2, ... ASC|DESC;
Request As a visual cue
The accompanying SQL explanation chooses all clients from the "Clients" table, arranged by the "Nation" segment:
SELECT * FROM Clients
Request BY Country;
The accompanying outlines the sentence structure of the Request BY proviso:
SELECT
select_list
FROM
table_name
Request BY
column1 [ASC|DESC],
column2 [ASC|DESC],
...;
In this punctuation, you determine the at least one sections that you need to sort after the Request BY proviso.
The ASC represents rising and the DESC represents plunging. You use ASC to sort the outcome set in rising request and DESC to sort the outcome set in plunging request separately.
This Request BY provision sorts the outcome set by the qualities in the column1 in climbing request:
Request BY column1 ASC;
Code language: SQL (Organized Inquiry Language) (sql)
Also, this Request BY provision sorts the outcome set by the qualities in the column1 in slipping request:
Request BY column1 DESC;
Code language: SQL (Organized Inquiry Language) (sql)
Naturally, the Request BY provision utilizes ASC in the event that you expressly determine no choice. Hence, the accompanying Request BY conditions are same:
Request BY column1 ASC;
Code language: SQL (Organized Inquiry Language) (sql)
Request BY column1;
Code language: SQL (Organized Inquiry Language) (sql)
To sort the outcome set by numerous segments, you determine a comma-isolated rundown of sections in the Request BY condition:
Request BY
column1,
column2;
Code language: SQL (Organized Inquiry Language) (sql)
For this situation, the Request BY statement sorts the outcome set by column1 in climbing request first and sorts the arranged outcome set by column2 in rising request.
It is feasible to sort the outcome set by a segment in rising request and afterward by one more section in dropping request:
Request BY
column1 ASC,
column2 DESC;
Code language: SQL (Organized Inquiry Language) (sql)
For this situation, the Request BY provision:
To start with, sort the outcome set by the qualities in the column1 in rising request.
Then, at that point, sort the arranged outcome set by the qualities in the column2 in plunging request. Note that the request for values in the column1 won't change in this step, just the request for values in the column2 changes.
While executing the SELECT assertion with a Request BY statement, MySQL generally assesses the Request BY provision after the FROM and SELECT conditions:
MySQL Request BY models
We'll utilize the clients table from the example data set for the showing.
A) Utilizing MySQL Request BY proviso to sort the outcome set by one segment model
The accompanying question utilizes the Request BY condition to sort the clients by their last names in rising request.
SELECT
contactLastname,
contactFirstname
FROM
clients
Request BY
contactLastname;
Code language: SQL (Organized Inquiry Language) (sql)
Give It A shot
Yield:
+-----------------+------------------+
| contactLastname | contactFirstname |
+-----------------+------------------+
| Accorti | Paolo |
| Altagar,G M | Raanan |
| Andersen | Mel |
| Anton | Carmen |
| Ashworth | Rachel |
| Barajas | Miguel |
...
Code language: plaintext (plaintext)
If you have any desire to sort clients by the last name in plummeting request, you utilize the DESC after the contactLastname section in the Request BY condition as displayed in the accompanying question:
SELECT
contactLastname,
contactFirstname
FROM
clients
Request BY
contactLastname DESC;
Code language: SQL (Organized Inquiry Language) (sql)
Give It A shot
Ouptut:
+-----------------+------------------+
| contactLastname | contactFirstname |
+-----------------+------------------+
| Youthful | Jeff |
| Youthful | Julie |
| Youthful | Mary |
| Youthful | Dorothy |
| Yoshido | Juri |
| Walker | Brydey |
| Victorino | Wendy |
| Urs | Braun |
| Tseng | Jerry |
....
Code language: plaintext (plaintext)
B) Utilizing MySQL Request BY proviso to sort the outcome set by various sections model
To sort the clients by the last name in dropping request and afterward by the main name in climbing request, you determine both DESC and ASC in these separate sections as follows:
SELECT
contactLastname,
contactFirstname
FROM
clients
Request BY
contactLastname DESC ,
contactFirstname ASC;
Code language: SQL (Organized Inquiry Language) (sql)
Give It A shot
Yield:
+-----------------+------------------+
| contactLastname | contactFirstname |
+-----------------+------------------+
| Youthful | Dorothy |
| Youthful | Jeff |
| Youthful | Julie |
| Youthful | Mary |
| Yoshido | Juri |
| Walker | Brydey |
| Victorino | Wendy |
| Urs | Braun |
| Tseng | Jerry |
| Tonini | Daniel |
Code language: plaintext (plaintext)
In this model, the Request BY proviso sorts the outcome set by the last name in sliding request first and afterward sorts the arranged outcome set by the main name in rising request to make the end-product set.
C) Utilizing MySQL Request BY statement to sort an outcome set by an articulation model
See the accompanying orderdetails table from the example information base.
order_details_table
The accompanying question chooses the request details from the orderdetails table. It computes the subtotal for each detail and sorts the outcome set in view of the subtotal.
SELECT
orderNumber,
orderlinenumber,
quantityOrdered * priceEach
FROM
orderdetails
Request BY
quantityOrdered * priceEach DESC;
Code language: SQL (Organized Question Language) (sql)
Give It A shot
+-------------+-----------------+-----------------------------+
| orderNumber | orderlinenumber | quantityOrdered * priceEach |
+-------------+-----------------+-----------------------------+
| 10403 | 9 | 11503.14 |
| 10405 | 5 | 11170.52 |
| 10407 | 2 | 10723.60 |
| 10404 | 3 | 10460.16 |
| 10312 | 3 | 10286.40 |
...
Code language: plaintext (plaintext)
To make the question more clear, you can relegate the articulation in the SELECT condition a section pseudonym and utilize that segment nom de plume in the Request BY statement as displayed in the accompanying inquiry:
SELECT
orderNumber,
orderLineNumber,
quantityOrdered * priceEach AS subtotal
FROM
orderdetails
Request BY subtotal DESC;
Code language: SQL (Organized Inquiry Language) (sql)
Give It A shot
+-------------+-----------------+----------+
| orderNumber | orderLineNumber | subtotal |
+-------------+-----------------+----------+
| 10403 | 9 | 11503.14 |
| 10405 | 5 | 11170.52 |
| 10407 | 2 | 10723.60 |
| 10404 | 3 | 10460.16 |
| 10312 | 3 | 10286.40 |
| 10424 | 6 | 10072.00 |
| 10348 | 8 | 9974.40 |
| 10405 | 3 | 9712.04 |
| 10196 | 5 | 9571.08 |
| 10206 | 6 | 9568.73 |
...
Code language: plaintext (plaintext)
In this model, we use subtotal as the segment pseudonym for the articulation quantityOrdered * priceEach and sort the outcome set by the subtotal moniker.
Since MySQL assesses the SELECT provision before the Request BY proviso, you can utilize the segment assumed name determined in the SELECT condition in the Request BY statement.
Utilizing MySQL Request BY statement to sort information utilizing a custom rundown
The FIELD() capability has the accompanying punctuation:
FIELD(str, str1, str2, ...)
Code language: SQL (Organized Inquiry Language) (sql)
The FIELD() capability returns the place of the str in the str1, str2, … list. If the str isn't in the rundown, the FIELD() capability brings 0 back. For instance, the accompanying inquiry returns 1 in light of the fact that the place of the string 'A' is the primary situation on the rundown 'A', 'B', and 'C':
SELECT FIELD('A', 'A', 'B','C');
Code language: SQL (Organized Inquiry Language) (sql)
Yield:
+--------------------------+
| FIELD('A', 'A', 'B','C') |
+--------------------------+
| 1 |
+--------------------------+
1 line in set (0.00 sec)
Code language: plaintext (plaintext)
Furthermore, the accompanying model brings 2 back:
SELECT FIELD('B', 'A','B','C');
Code language: SQL (Organized Question Language) (sql)
Yield:
+-------------------------+
| FIELD('B', 'A','B','C') |
+-------------------------+
| 2 |
+-------------------------+
1 column in set (0.00 sec)
Code language: plaintext (plaintext)
We should take a more useful model.
See the accompanying requests table from the example information base.
orders table
Assume that you need to sort the deals orders in light of their situations with the accompanying request:
In Cycle
On Hold
Dropped
Settled
Questioned
Delivered
To do this, you can utilize the FIELD() capability to plan each request status to a number and sort the outcome by the consequence of the FIELD() capability:
SELECT
orderNumber, status
FROM
orders
0 Comments