ORDER BY in MySQL

INTRODUCTION

 
In this tutorial, I am going to explain about ORDER BY clause in MySQL with examples. Without wasting time, let’s start.
 

ORDER BY 

 
ORDER BY will tell the MySQL server to sort the rows by a column. Define in which direction to sort, as the order of the returned rows may not yet be meaningful. Rows can be returned in ascending or descending order.
 
Then, you query your MySQL database, you can sort the results by any field in an ascending or descending order by just adding 'ORDER BY' at the end of your query. You would use ORDER BY field_name ASC for an ascending order or ORDER BY field_name DESC for a descending order.
 
In MySQL, an Order By is used to sort the data in a record set. You can also use multiple sorting criteria separated by commas.
 
Syntax:
SELECT col1, col2, col3, …coln
FROM table_name
ORDER BY
   Col1 [ASC|DESC],
   Col2 [ASC|DESC]...
 
Note: You can specify more than one column name in the ORDER BY clause that you want to sort.
 
User can sort their data (resultset) in two different formats, ASC or DESC. ‘ASC’ stands for ascending order or ‘DESC’ stands for descending order.
 
Syntax:
ORDER BY Column_name ASC;
ORDER BY Column_name DESC;
 
Note: By default, the ORDER BY clause sorts the records (result set) in ascending order.
 
ORDER BY Column_name ASC;
ORDER BY Column_name; and ORDER BY Column_name ASC;
 
Both ORDER BY clauses are equivalent.
 
If you want to sort multiple columns specify all the columns by a comma.
 
SELECT column1, column2, …column
FROM table_name
ORDER BY
 column1 DESC,
 column2 ASC;
 
Here, the result is sorted by column1 in descending order first then, result is sorted by column2 in ascending order.
 
So, without wasting time, let’s see some examples. First, we have to create a database and a few tables and then insert some records into them. I have also attached the sample database here.
 
Create a database
  1. CREATE DATABASE ORDER_BY; 
Create a StudentDetails table
  1. USE ORDER_BY; 
  1. CREATE TABLE StudentDetails (  
  2.   StudentNumber int NOT NULL,  
  3.   StudentName varchar(50) NOT NULL,  
  4.   contactLastName varchar(50) NOT NULL,  
  5.   contactFirstName varchar(50) NOT NULL,  
  6.   contactnumber varchar(50) NOT NULL,  
  7.   addressLine1 varchar(250) NOT NULL,  
  8.   addressLine2 varchar(250) DEFAULT NULL,  
  9.   city varchar(50) NOT NULL,  
  10.   state varchar(50) DEFAULT NULL,  
  11.   postalCode varchar(15) DEFAULT NULL,  
  12.   country varchar(50) NOT NULL,  
  13.   PRIMARY KEY (StudentNumber)  
  14. ); 
Insert records into it
  1. INSERT INTO StudentDetails(StudentNumber, StudentName, contactLastName, contactFirstName, contactnumber, addressLine1, addressLine2, city, state, postalCode, country) VALUES    
  2. (1, 'Atul''Techie''Car''9876543210''544, rue Royale'NULL'noida'NULL'44000''India'),    
  3. (2, 'Simran''King''John''9876543210''844 Strong St.'NULL'Delhi'NULL'83030''India'),    
  4. (3, 'Alok''Person''Petar''9876543210''787 St Kilda Road''Level 3''Mumbai'NULL'3004''India'),    
  5. (4, 'Rohit''Techie''AI ''9876543210''778, rue des Cinquante Otages'NULL'Delhi'NULL'44000''India'),    
  6. (5, 'Onkar''Royal''Vatsa''9876543210''Erling Skakkes gate 787'NULL'Bulandshahr'NULL'4110''India'),    
  7. (6, 'Manish.''son''Indian''9876543210''787 Strong St.'NULL'San'NULL'97562''India'),    
  8. (7, 'Vaishali''zen''Zzek ''9876543210''ul. Filtrowa 787'NULL'Warszawa'NULL'01-012''India'),    
  9. (8, 'Boss''Ketel''Roan''9876543210''Lyonerstr. 787'NULL'Frankfurt'NULL'60528''India'),    
  10. (9, 'Mini''Murphy''Jule''9876543210''7878 North Pendale Street'NULL'San Francisco'NULL'94217''India'),    
  11. (10, 'Lakshay''Leaa''Kwi''9876543210''898 Long Airport Avenue'NULL'NYC'NULL'10022''India'),    
  12. (11, 'Sandeep''Free''Dongo''9876543210''C/ Moralzarzal, 787'NULL'Madrid'NULL'28034''India'),    
  13. (12, 'Anamika''Berg''Chris''9876543210''Berguvsvägen  787'NULL'lucknow'NULL'S-958 22''India'),    
  14. (13, 'Divya''Petersen''Jtte''9876543210''Vinbæltet 787'NULL'Delhi'NULL'1734''India'),    
  15. (14, 'Sonam''Save''Maryy''9876543210''787, rue du Commerce'NULL'Delhi'NULL'69004''India'),    
  16. (15, 'Reetika''Native''Epic''9876543210''Bronz Sok.''Bronz Apt. 3/6 Tesvikiye''Delhi'NULL'079903''India'),    
  17. (16, 'Neetika''bejoj''Jeff''9876543210''78787 Furth Circle''Suite 400''Delhi'NULL'10022''India'),    
  18. (17, 'Meetika''Long''Kelvin''9876543210''8787 Pompton St.'NULL'Delhi'NULL'70267''India'),    
  19. (18, 'Riya''Hash''July''9876543210''8787 Furth Circle'NULL'Delhi'NULL'94217''India'),    
  20. (19, 'Rhea''Victoria''Wendy''9876543210''8787 Linden Road Sandown''2nd Floor''Delhi'NULL'069045''India'),    
  21. (20, 'Bina''tan''Veyshali''9876543210''Brehmen St. 8787''PR 334 Sentrum''Delhi'NULL'N 5804''India'),    
  22. (21, 'Deepak''Franco''Keith''9876543210''87787 Spinnaker Dr.''Suite 101''New Delhi'NULL'97823''India'),    
  23. (22, 'Daya''abbca''naina''9876543210''Estrada da saúde n. 8787'NULL'Delhi'NULL'1756''India'),    
  24. (23, 'Neha''reeka''mona''9876543210''87878, chaussée de Tournai'NULL'Delhi'NULL'59000''India'),    
  25. (24, 'Nupur''senon''ne''9876543210''8787, boulevard Charonne'NULL'Delhi'NULL'75012''India'),    
  26. (25, 'Shikha''chikki''cheey''9876543210''78787 Baden Av.'NULL'New Delhi'NULL'51247''India');   
    Create a Library table
    1. CREATE TABLE Library(  
    2.     BookNumber int NOT NULL,  
    3.     BookCode varchar(15) NOT NULL,  
    4.     BookIssue int NOT NULL,  
    5.     CostEach decimal(10, 2) NOT NULL,  
    6.     PRIMARY KEY(BookNumber, BookCode)  
    7. ); 
    Now, insert some records into it.
    1. INSERT INTO Library(BookNumber, BookCode, BookIssue, CostEach) VALUES  
    2. (10100, 'B18_1749', 30, '136.00'),  
    3. (10100, 'B18_2248', 50, '55.09'),  
    4. (10101, 'B18_2325', 25, '108.06'),  
    5. (10101, 'B18_2795', 26, '167.06'),  
    6. (10102, 'B18_1342', 39, '95.55'),  
    7. (10102, 'B18_1367', 41, '43.13'),  
    8. (10103, 'B10_1949', 26, '214.30'),  
    9. (10103, 'B10_4962', 42, '119.67'),  
    10. (10103, 'B18_2432', 22, '58.34'),  
    11. (10103, 'B18_2949', 27, '92.19'),  
    12. (10104, 'B18_3232', 23, '165.95'),  
    13. (10104, 'B18_4027', 38, '119.20'),  
    14. (10104, 'B24_1444', 35, '52.02'),  
    15. (10104, 'B24_2840', 44, '30.41'),  
    16. (10104, 'B24_4048', 26, '106.45'),  
    17. (10105, 'B24_2011', 43, '117.97'),  
    18. (10105, 'B24_3151', 44, '73.46'),  
    19. (10105, 'B24_3816', 50, '75.47'),  
    20. (10105, 'B700_1138', 41, '54.00'),  
    21. (10105, 'B700_1938', 29, '86.61'),  
    22. (10106, 'B18_3856', 41, '94.22'),  
    23. (10106, 'B24_1785', 28, '107.23'),  
    24. (10106, 'B24_2841', 49, '65.77'),  
    25. (10106, 'B24_3420', 31, '55.89'),  
    26. (10106, 'B24_3949', 50, '55.96'),  
    27. (10107, 'B12_2823', 21, '122.00'),  
    28. (10107, 'B18_2625', 29, '52.70'),  
    29. (10107, 'B24_1578', 25, '96.92'),  
    30. (10107, 'B24_2000', 38, '73.12'),  
    31. (10107, 'B32_1374', 20, '88.90'),  
    32. (10108, 'B24_4620', 31, '67.10'),  
    33. (10108, 'B32_2206', 27, '36.21'),  
    34. (10108, 'B32_4485', 31, '87.76'),  
    35. (10108, 'B50_4713', 34, '74.85'),  
    36. (10109, 'B18_1129', 26, '117.48'),  
    37. (10109, 'B18_1984', 38, '137.98'),  
    38. (10109, 'B18_2870', 26, '126.72'),  
    39. (10109, 'B18_3232', 46, '160.87'),  
    40. (10110, 'B24_2887', 46, '112.74'),  
    41. (10110, 'B24_3191', 27, '80.47'),  
    42. (10110, 'B24_3432', 37, '96.37'),  
    43. (10110, 'B24_3969', 48, '35.29'),  
    44. (10111, 'B18_1342', 33, '87.33'),  
    45. (10111, 'B18_1367', 48, '48.52'),  
    46. (10111, 'B18_2957', 28, '53.09'),  
    47. (10111, 'B18_3136', 43, '94.25'),  
    48. (10112, 'B10_1949', 29, '197.16'),  
    49. (10112, 'B18_2949', 23, '85.10'),  
    50. (10113, 'B12_1666', 21, '121.64'),  
    51. (10113, 'B18_1097', 49, '101.50'),  
    52. (10113, 'B18_4668', 50, '43.27'),  
    53. (10113, 'B32_3522', 23, '58.82'),  
    54. (10114, 'B10_4962', 31, '128.53'),  
    55. (10114, 'B18_2319', 39, '106.78'),  
    56. (10114, 'B18_2432', 45, '53.48'),  
    57. (10114, 'B18_3232', 48, '169.34'),  
    58. (10115, 'B18_2238', 46, '140.81'),  
    59. (10115, 'B24_1444', 47, '56.64'),  
    60. (10115, 'B24_4048', 44, '106.45'),  
    61. (10115, 'B50_1392', 27, '100.70'),  
    62. (10116, 'B32_3207', 27, '60.28'),  
    63. (10117, 'B12_1108', 33, '195.33'),  
    64. (10117, 'B12_3148', 43, '148.06'),  
    65. (10117, 'B12_3891', 39, '173.02'),  
    66. (10117, 'B18_3140', 26, '121.57'),  
    67. (10118, 'B700_3505', 36, '86.15'),  
    68. (10119, 'B10_4757', 46, '112.88'),  
    69. (10119, 'B18_1662', 43, '151.38'),  
    70. (10119, 'B18_3029', 21, '74.84'),  
    71. (10119, 'B18_3856', 27, '95.28'),  
    72. (10120, 'B700_1691', 47, '91.34'),  
    73. (10120, 'B700_2466', 24, '81.77'),  
    74. (10120, 'B700_2834', 24, '106.79'),  
    75. (10120, 'B700_3167', 43, '72.00'),  
    76. (10121, 'B10_1678', 34, '86.13'),  
    77. (10121, 'B12_2823', 50, '126.52'),  
    78. (10121, 'B24_2360', 32, '58.18'),  
    79. (10121, 'B32_4485', 25, '95.93'),  
    80. (10121, 'B50_4713', 44, '72.41'); 
     
    Create a BookOrder table
    1. CREATE TABLE Bookorder(  
    2.     BookNumber int NOT NULL,  
    3.     orderDate date NOT NULL,  
    4.     shippedDate date DEFAULT NULL,  
    5.     Status varchar(50),  
    6.     PRIMARY KEY(BookNumber)  
    7. ); 
    Insert some records into it
    1. INSERT INTO BookOrder(BookNumber, orderDate, shippedDate, Status) VALUES    
    2. (10100, '2003-01-06''2020-01-10''Done'),    
    3. (10101, '2020-01-09''2020-01-11''Done'),    
    4. (10102, '2020-01-10''2020-01-14''Done'),    
    5. (10103, '2020-01-29''2020-02-02''Done'),    
    6. (10104, '2020-01-31''2020-02-01''Done'),    
    7. (10105, '2020-02-11''2020-02-12''Done'),    
    8. (10106, '2020-02-17''2020-02-21''In progress'),    
    9. (10107, '2020-02-24''2020-02-26''Done'),    
    10. (10108, '2020-03-03''2020-03-08''Done'),    
    11. (10109, '2020-03-10''2020-03-11''Done'),    
    12. (10110, '2020-03-18''2020-03-20''In progress'),    
    13. (10111, '2020-03-25''2020-03-30''Done'),    
    14. (10112, '2020-03-24''2020-03-29''Done'),    
    15. (10113, '2020-03-26''2020-03-27''In progress'),    
    16. (10114, '2020-04-01''2020-04-02''Done'),    
    17. (10115, '2020-04-04''2020-04-07''Done'),    
    18. (10116, '2020-04-11''2020-04-13''In progress'),    
    19. (10117, '2020-04-16''2020-04-17''Done'),    
    20. (10118, '2020-04-21''2020-04-26''In progress'),    
    21. (10119, '2020-04-28''2020-05-02''Done'),    
    22. (10120, '2020-04-29''2020-05-01''Done'),    
    23. (10121, '2020-05-07''2020-05-13''Done'); 

    A)  SORT THE VALUES USING MYSQL ORDER BY CLAUSE

     
    Here, I am using the ORDER BY clause to sort the data in ascending order. Query:
    1. SELECT contactLastname, contactFirstname, StudentName  
    2. FROM studentdetails  
    3. ORDER BY contactLastname; 
     
    If you want to sort the result in descending order, then use DESC keyword after the ORDER BY clause.
    1. SELECT contactLastname, contactFirstname, StudentName  
    2. FROM studentdetails  
    3. ORDER BY contactLastname DESC
     

    B)  SORT THE RESULT BY MULTIPLE COLUMNS USING MYSQL ORDER BY CLAUSE

     
    If you want to sort the result by the contactLastname in ascending order, and then by contactFirstname in ascending order. Then, you have to use multiple columns in ORDER BY clause specifying both ASC and DESC keywords.
    1. SELECT contactlastname, contactFirstname  
    2. FROM studentdetails  
    3. ORDER BY contactlastname DESC,  
    4.          contactFirstname ASC
     
    Here, the result is sort by ‘contactlastname‘ in descending order then, sorted result by ‘contactFirstname’ in ascending order.
     

    C)  SORT THE RESULT BY AN EXPRESSION USING MySQL ORDER BY CLAUSE

     
    Now, use the ‘Library’ table from the sample database.
    1. SELECT BookNumber, BookCode, (Bookissue * costeach) AS Total_Cost  
    2. FROM Library  
    3. ORDER BY Booknumber ASC,  
    4.          BookCode ASC,  
    5.          Total_cost ; 
     

    D) SORT THE RESULT BY USING A CUSTOM LIST

     
    Now, use the ‘BookOrder’ table from the sample database. Using the FIELD function, the ORDER BY clause lets you order results using a custom list. Let’s see.
    Here, you can sort your ‘BookOrder’ data based on their status, i.e., In progress, and Done.
    1. SELECT BookNumber, orderdate, shippedDate, status  
    2. FROM BookOrder  
    3. ORDER BY FIELD (Status, 'In Progress''Done'); 
     

    CONCLUSION

     
    In this article, I have discussed the concept of ORDER BY clause in MySQL with various examples.
     
    I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.
     
    Thanks for reading this article!