TechBeamersTechBeamers
  • Learn ProgrammingLearn Programming
    • Python Programming
      • Python Basic
      • Python OOP
      • Python Pandas
      • Python PIP
      • Python Advanced
      • Python Selenium
    • Python Examples
    • Selenium Tutorials
      • Selenium with Java
      • Selenium with Python
    • Software Testing Tutorials
    • Java Programming
      • Java Basic
      • Java Flow Control
      • Java OOP
    • C Programming
    • Linux Commands
    • MySQL Commands
    • Agile in Software
    • AngularJS Guides
    • Android Tutorials
  • Interview PrepInterview Prep
    • SQL Interview Questions
    • Testing Interview Q&A
    • Python Interview Q&A
    • Selenium Interview Q&A
    • C Sharp Interview Q&A
    • PHP Interview Questions
    • Java Interview Questions
    • Web Development Q&A
  • Self AssessmentSelf Assessment
    • Python Test
    • Java Online Test
    • Selenium Quiz
    • Testing Quiz
    • HTML CSS Quiz
    • Shell Script Test
    • C/C++ Coding Test
Search
  • Python Multiline String
  • Python Multiline Comment
  • Python Iterate String
  • Python Dictionary
  • Python Lists
  • Python List Contains
  • Page Object Model
  • TestNG Annotations
  • Python Function Quiz
  • Python String Quiz
  • Python OOP Test
  • Java Spring Test
  • Java Collection Quiz
  • JavaScript Skill Test
  • Selenium Skill Test
  • Selenium Python Quiz
  • Shell Scripting Test
  • Latest Python Q&A
  • CSharp Coding Q&A
  • SQL Query Question
  • Top Selenium Q&A
  • Top QA Questions
  • Latest Testing Q&A
  • REST API Questions
  • Linux Interview Q&A
  • Shell Script Questions
© 2024 TechBeamers. All Rights Reserved.
Reading: MySQL ORDER BY with Simple Examples
Font ResizerAa
TechBeamersTechBeamers
Font ResizerAa
  • Python
  • SQL
  • C
  • Java
  • Testing
  • Selenium
  • Agile Concepts Simplified
  • Linux
  • MySQL
  • Python Quizzes
  • Java Quiz
  • Testing Quiz
  • Shell Script Quiz
  • WebDev Interview
  • Python Basic
  • Python Examples
  • Python Advanced
  • Python OOP
  • Python Selenium
  • General Tech
Search
  • Programming Tutorials
    • Python Tutorial
    • Python Examples
    • Java Tutorial
    • C Tutorial
    • MySQL Tutorial
    • Selenium Tutorial
    • Testing Tutorial
  • Top Interview Q&A
    • SQL Interview
    • Web Dev Interview
  • Best Coding Quiz
    • Python Quizzes
    • Java Quiz
    • Testing Quiz
    • ShellScript Quiz
Follow US
© 2024 TechBeamers. All Rights Reserved.
MySQL Tutorial

MySQL ORDER BY with Simple Examples

Last updated: May 22, 2023 12:52 pm
By Meenakshi Agarwal
Share
8 Min Read
MySQL ORDER BY with ASC DESC Examples
MySQL ORDER BY with Simple Examples
SHARE

This tutorial explains MySQL ORDER BY clause and its usage with both the ASC and DESC options. We’ll describe this MySQL statement with the help of simple examples.

Contents
SyntaxMySQL ORDER BY Clause ExamplesUsing order by without asc/desc optionSort in descending orderSort by field on a relative positionUsing ORDER BY to sort on two columns

1. ORDER BY Clause Syntax
2. ORDER BY W/O ASC or DESC
3. ORDER BY DESC
4. ORDER BY Relative Position
5. ORDER BY Two Fields ASC & DESC

Let’s now go through each of the sections one by one.

MySQL ORDER BY Clause

The MySQL SELECT command doesn’t return a sorted result set by default. Hence, to sort the output, you have to append the ORDER BY clause in the SELECT query. We can achieve the following by using the ORDER BY clause:

  • Perform sorting of records by a single/multiple fields
  • Sort the rows by distinct columns in ascending (ASC) or descending (DESC) order.

The following illustrates the syntax of the ORDER BY clause:

Syntax

# MySQL ORDER BY Clause
SELECT field1, field2,...
FROM target_table
ORDER BY field1 [ASC|DESC], field2 [ASC|DESC],...

The ORDER BY clause by default performs sorting in ascending order. The term ASC is an acronym for increasing while DESC means descending. For DESC, you have to mention it in your SELECT query.

Please note that you can even specify an expression with the ORDER BY in MySQL. Also, both the options ASC and DESC are optional to this statement.

Here are some sample exercises for practice.

MySQL ORDER BY Clause Examples

Let’s check out how to use the ORDER BY clause with MySQL SELECT queries.

Using order by without asc/desc option

To demonstrate, let’s first create a table named Sample and populate it with some test data.

CREATE TABLE Sample
    (
        sample_id int NOT NULL,
        sample_name VARCHAR(20),
        vendor_name VARCHAR(20),
        sample_size INT
    );

Next, let’s insert some dummy data for our testing.

INSERT INTO Sample
    (
        sample_id, sample_name, vendor_name, sample_size
    )
VALUES
    (1, 'First Sample', 'Walmart', 100),
    (2, 'Second Sample', 'Twitter', 1000),
    (3, 'Third Sample', 'Google', 300),
    (4, 'Fourth Sample', 'Apple', 4000);

After this, we need to check the content of our Sample table. See below.

SELECT 
    *
FROM
    Sample;

Its output is as follows:

ID      Sample Name     Vendor  Size
-------------------------------------
1	First Sample	Walmart	100
2	Second Sample	Twitter	1000
3	Third Sample	Google	300
4	Fourth Sample	Apple	4000

Now, we’ll exercise the ORDER BY clause on the “vendor_name” filed. And, we’ll observe the result set received from the MySQL database:

SELECT 
    *
FROM
    Sample
ORDER BY vendor_name;

You’ll get a result showing the below output:

ID      Sample Name     ↑Vendor  Size
-------------------------------------
4	Fourth Sample	Apple	4000
3	Third Sample	Google	300
2	Second Sample	Twitter	1000
1	First Sample	Walmart	100

You can assess from the above result set that sorting took place in the default ascending order.

Sort in descending order

In this example, we’ll demonstrate the ORDER BY clause to sort in the descending order. For this, we’ll have to specify it explicitly.

Here is the MySQL statement to sort in the descending order:

SELECT 
    *
FROM
    Sample
ORDER BY vendor_name DESC;

The output of SELECT is:

ID      Sample Name     ↓Vendor  Size
-------------------------------------
1	First Sample	Walmart	100
2	Second Sample	Twitter	1000
3	Third Sample	Google	300
4	Fourth Sample	Apple	4000

Sort by field on a relative position

It is possible to use MySQL ORDER BY clause to sort by a field on a RELATIVE position.

When we specify column names in the SELECT query, the first field is at position one, second on two, and so on. Moreover, we can validate this fact using an example.

Here is a SELECT statement that displays fields and sorts by a relative position.

SELECT 
#   pos_1      pos_2        pos_3        pos_4
    sample_id, sample_name, vendor_name, sample_size
FROM
    Sample
ORDER BY 4 DESC;

Here is the result set that you get after running the above command:

ID      Sample Name     Vendor  ↓Size
-------------------------------------
4	Fourth Sample	Apple	4000
2	Second Sample	Twitter	1000
3	Third Sample	Google	300
1	First Sample	Walmart	100

You can see that MySQL sorted by sample_size field which corresponds to the fourth column.

Using ORDER BY to sort on two columns

We had mentioned earlier that you could use ORDER BY on one or more fields. In such a case, MySQL treats the first field as primary and the latter as secondary. Therefore, it first sorts the primary and then the second one.

Hence, in this example, we’ll demonstrate ORDER BY on two columns, one field as ASC, and another DESC. Also, we need to insert more records to show that the second column gets sorted too.

You can ignore the INSERT statement given at the beginning above and use the one below.

INSERT INTO Sample
    (
        sample_id, sample_name, vendor_name, sample_size
    )
VALUES
    (1, 'First Sample', 'Walmart', 100),
    (2, 'Second SampleI', 'Twitter', 1000),
    (2, 'Second SampleII', 'Twitter', 9000),
    (3, 'Third SampleI', 'Google', 400),
    (3, 'Third SampleII', 'Google', 800),
    (4, 'Fourth Sample', 'Apple', 4000);

Now, we have a few rows with the same vendor names but varied sample sizes. And, we’ll now sort based upon vendor_name (primary) and sample_size (secondary) fields.

SELECT 
    sample_id, sample_name, vendor_name, sample_size
FROM
    Sample
ORDER BY vendor_name ASC, sample_size DESC;

This MySQL statement produces this result:

ID      Sample Name     ↑Vendor  ↓Size
-------------------------------------
4	Fourth Sample	Apple	4000
3	Third SampleII	Google	↓800
3	Third SampleI	Google	↓400
2	Second SampleII	Twitter	↓9000
2	Second SampleI	Twitter	↓1000
1	First Sample	Walmart	100

You can see all the values in the primary field got sorted in the ascending order. And, it is also evident from the output that sorting (in DESC) took place on the second one as well.

Now, let’s find out what would be the result, had we sorted only on the vendor name.

SELECT 
    sample_id, sample_name, vendor_name, sample_size
FROM
    Sample
ORDER BY vendor_name ASC;

This command will yield as:

ID      Sample Name     ↑Vendor  Size
-------------------------------------
4	Fourth Sample	Apple	4000
3	Third SampleI	Google	(x)400
3	Third SampleII	Google	800
2	Second SampleI	Twitter	(x)1000
2	Second SampleII	Twitter	9000
1	First Sample	Walmart	100

We can see from the above result that rows belonging to Google and Twitter didn’t sort on their sample sizes.

We hope that after wrapping up this tutorial, you should feel comfortable in using the MySQL ORDER BY clause. However, you may practice more with examples to gain confidence.

Also, to learn SQL from scratch to depth, do read our step by step MySQL tutorial.

You Might Also Like

MySQL vs MongoDB Comparison

Concatenate Strings in an SQL Query With Examples

The Difference between UPSERT & Insert

SQL Programming Test in 2024

SQL Table Creation: The Missing Manual

Meenakshi Agarwal Avatar
By Meenakshi Agarwal
Follow:
Hi, I'm Meenakshi Agarwal. I have a Bachelor's degree in Computer Science and a Master's degree in Computer Applications. After spending over a decade in large MNCs, I gained extensive experience in programming, coding, software development, testing, and automation. Now, I share my knowledge through tutorials, quizzes, and interview questions on Python, Java, Selenium, SQL, and C# on my blog, TechBeamers.com.
Previous Article MySQL Data Types Explained with Examples MySQL Data Types Explained
Next Article User Story Template for Agile teams User Story Template in Agile Scrum

Popular Tutorials

SQL Interview Questions List
50 SQL Practice Questions for Good Results in Interview
SQL Interview Nov 01, 2016
Demo Websites You Need to Practice Selenium
7 Sites to Practice Selenium for Free in 2024
Selenium Tutorial Feb 08, 2016
SQL Exercises with Sample Table and Demo Data
SQL Exercises – Complex Queries
SQL Interview May 10, 2020
Java Coding Questions for Software Testers
15 Java Coding Questions for Testers
Selenium Tutorial Jun 17, 2016
30 Quick Python Programming Questions On List, Tuple & Dictionary
30 Python Programming Questions On List, Tuple, and Dictionary
Python Basic Python Tutorials Oct 07, 2016
//
Our tutorials are written by real people who’ve put in the time to research and test thoroughly. Whether you’re a beginner or a pro, our tutorials will guide you through everything you need to learn a programming language.

Top Coding Tips

  • PYTHON TIPS
  • PANDAS TIPSNew
  • DATA ANALYSIS TIPS
  • SELENIUM TIPS
  • C CODING TIPS
  • GDB DEBUG TIPS
  • SQL TIPS & TRICKS

Top Tutorials

  • PYTHON TUTORIAL FOR BEGINNERS
  • SELENIUM WEBDRIVER TUTORIAL
  • SELENIUM PYTHON TUTORIAL
  • SELENIUM DEMO WEBSITESHot
  • TESTNG TUTORIALS FOR BEGINNERS
  • PYTHON MULTITHREADING TUTORIAL
  • JAVA MULTITHREADING TUTORIAL

Sign Up for Our Newsletter

Subscribe to our newsletter to get our newest articles instantly!

Loading
TechBeamersTechBeamers
Follow US
© 2024 TechBeamers. All Rights Reserved.
  • About
  • Contact
  • Disclaimer
  • Privacy Policy
  • Terms of Use
TechBeamers Newsletter - Subscribe for Latest Updates
Join Us!

Subscribe to our newsletter and never miss the latest tech tutorials, quizzes, and tips.

Loading
Zero spam, Unsubscribe at any time.
x