Order By in MySQL Queries

Using a select query, if you want to fetch data in the order of an attribute, the order by the statement is necessary.

Let us retrieve the name, author, publisher, published on, and price information from the book table. The result is shown in the following table.

You can check that the tuples are not sorted in order of any attribute. Thus, if you want to retrieve the data in order of any attribute, you can use order by statement with select queries. The following table shows results where data has been retrieved in order of book name.

By default the sorting will be performed in ascending order, if you want to sort them in reverse (descending) order, you may use desc at the end, as order by name desc.

The result in order of price is shown in the following table.

Now, we are preparing the table with more data to understand order by on multiple attributes. After truncating the table the following SQL queries are being executed to insert data.

insert into books values ('C Programming Language', 2, '9780131103627', 'Brian W. Kernighan, Dennis M. Ritchie', 'Paperback', 'Prentice Hall', 'April 1988', 67.00);

 

insert into books values ('Java: A Beginners Guide', 7, '9781259589317', 'Herbert Schildt', 'Paperback', 'McGraw-Hill Education', 'October 2017', 40.00);

 

insert into books values ('Python Programming', 1, '9781590282755', 'Zelle, John', 'Paperback', 'Franklin, Beedle & Associates', 'April 2014', 70.00);

 

insert into books values ('Design and Analysis of Algorithm', 1, '9781976735974', 'BHUPENDRA SINGH MANDLOI', 'Paperback', 'Independently published', 'January 2018', 5.00);

 

insert into books values ('Introduction to Algorithms', 3, '9780262033848', 'Thomas H. Cormen, Charles E. Leiserson, Ronald L. Rivest, Clifford Stein', 'Hardcover', 'The MIT Press', 'July 2009', 99.00);

 

insert into books values ('Operating Systems', 2, '9780985673529', 'Thomas Anderson, Michael Dahlin', 'Paperback', 'Recursive Books', 'August 2014', 72.00);

 

insert into books values ('C Programming Language', 2, '9780131103627', 'Brian W. Kernighan, Dennis M. Ritchie', 'Paperback', 'McGraw-Hill Education', 'April 1988', 67.00);

 

insert into books values ('Java: A Beginners Guide', 7, '9781259589317', 'Herbert Schildt', 'Paperback', 'McGraw-Hill Education', 'October 2017', 40.00);

 

insert into books values ('Python Programming', 1, '9781590282755', 'Zelle, John', 'Paperback', 'The MIT', 'April 2014', 70.00);

 

insert into books values ('Design and Analysis of Algorithm', 1, '9781976735974', 'BHUPENDRA SINGH MANDLOI', 'Paperback', 'McGraw-Hill Education', 'January 2018', 5.00);

 

insert into books values ('Introduction to Algorithms', 3, '9780262033848', 'Thomas H. Cormen, Charles E. Leiserson, Ronald L. Rivest, Clifford Stein', 'Hardcover', 'The MIT', 'July 2009', 99.00);

 

insert into books values ('Operating Systems', 2, '9780985673529', 'Thomas Anderson, Michael Dahlin', 'Paperback', 'McGraw-Hill Education', 'August 2014', 72.00);

Now, the table is shown below.

The order by can be used for multiple attributes. In that case, first, the tuples will be sorted based on the first attribute followed by other attributes in sequential order. In the following example, we have shown how multiple attributes can be associated with the order by.

Happy Exploring!