MySQL ORDER BY with Examples

MySQL ORDER BY Clause

MySQL ORDER BY with Examples
Image Credit : CBSE CS and IP

Summary :

  • In this Tutorial, you will learn how to sort a result set using MySQL ORDER clause.
  • In this section, we will be looking at how we sort our query results in a specified way.
  • Sorting can be performed on a single column or on more than one column. It can be done on number, strings as well as data types.

Introduction to MySQL ORDER BY clause

MySQL ORDER BY clause used in conjunction with the SELECT query to sort data in an orderly manner.
The result set generated by SQL SELECT statement is not ordered in any form by default.
Than MySQL ORDER BY clause is used to sort the query result sets in either ascending or descending order.

Syntax of the ORDER BY clause :

SELECT 
    statements...
FROM
     table name
WHERE
     condition...
ORDER BY
      column_name [ASC/DESC], 
      column_name2 [ASC/DESC], ....;

In the syntax,

  • “SELECT statement..” is regular query
  • “WHERE
    condition…” is optional query used to filter the query result sets.
  • “ORDER BY” performs the query result set sorting.
  • Keywords ASC and DESC denote the order – ASC stands for ascending and the DESC stands for descending.
  • If you do not specify any order keyword ASC or DESC, then by default, the ORDER BY clause sorts the result set in ascending order.
  • The ORDER BY clause for the result set in ascending order.
ORDER BY column_name ASC;
  • And the ORDER BY clause for result set in descending order.
ORDER BY column_name DESC;

Order BY clause in single column

To display the result set in Ascending Order.
For example, consider the table Data having records as shown below :

rollnonamemarksgradesection
101Ruhani76.80AA
102George71.20BA
103Simran81.20AB
104Ali61.20BC
105Kushal51.60CC
106Arsiya91.60A+B
107Raunaq32.50FB
108Meera97.20A+B
109Amaal57.20CB
111Simran66.00BA
112Adam74.20BC
113Gurnoor93.50A+B
115Rabiya72.50BB
117Rahil32.00FC
118Neha59.50CA
Query
SELECT * From data 
ORDER BY marks ASC;
Output
rollnonamemarksgradesection
117Rahil32.00FC
107Raunaq32.50FB
105Kushal51.60CC
109Ammal57.20CB
118Neha59.50CA
104Ali61.20BC
111Simran66.00BA
102George71.20BA
115Rabiya72.50BB
112Adam74.20BC
101Ruhani76.80AA
103Simran81.20AB
106Arsiya91.60A+B
113Gurnoor93.50A+B
108Meera97.20A+B

The above SQL statement is equivalent to statement shown below where ASC is not used.

Query
SELECT * From data 
ORDER BY marks ;
Output

is same as of above query.

To display the result set in descending order.

Query
SELECT * From Data 
ORDER BY marks DESC;
Output
rollnonamemarksgradesection
108Meera97.20A+B
113Gurnoor93.50A+B
106Arsiya91.60A+B
103Simran81.20AB
101Ruhani76.80AA
112Adam74.20BC
115Rabiya72.50BB
102George71.20BA
111Simran66.00BA
104Ali61.20BC
118Neha59.50CA
109Amaal57.20CB
105Kushal51.60CC
107Raunaq32.50FB
117Rahil32.00FC

Order BY clause in Multiple Columns

To order the result set on multiple columns, you can specify the multiple column names in ORDER BY clause along with the desired sort order, i.e., as :

SELECT
:
ORDER BY column_name [ASC/DESC], column_name2 [ASC/DESC], 

For example, the given below query will sort the records firstly on column name Section and then on the basis of descending order of column marks.

Query
SELECT * FROM Data
ORDER BY section ASC, marks DESC ;

First sort field is section in ascending order (as section ASC) and for all the records of same section, the sort field is marks with descending order (marks DESC)

Output
rollnonamemarksgradesection
101Ruhani76.80AA
102George71.20BA
111Simran66.00BA
118Neha59.50CA
108Meera97.20A+B
113Gurnoor93.50A+B
106Arsiya9160A+B
103Simran81.20AB
115Rabiya72.50BB
109Amaal57.20CB
107Raunaq32.50FB
112Adam74.20BC
104Ali61.20BC
105Kushal51.60CC
117Rahil32.00FC

MySQL ORDER BY clause to sort a result set by an expression


Sometimes, you need to display the result of calculation or a mathematical expression in the result set. In such cases, you may want or need to arrange your result set in the order of the calculated expression.
The ORDER BY clause allows you to include the mathematical expression to order the result set by it. However, to arrange a result set on the basis of a mathematical expression, you should preferably (though not a necessity but preferably) include the mathematical expression in the select list so that it becomes easy to comprehend the result.
Consider the following query statement that arranges the result on the basis of a calculated result :

Query
SELECT rollno, name, grade, section, marks*0.35 From data
WHERE marks > 70
ORDER BY section ASC, marks*0.35 DESC ;
Output
rollnonamegradesectionmarks*0.5
101RuhaniAA26.8800
102GeorgeBA24.9200
108MeeraA+B34.0200
113GurnoorA+B32.7250
106ArsiyaA+B32.0600
103SimranAB28.4200
115RabiyaBB25.3750
112AdamBC25.9700

If you want, you can provide a column alias name to the mathematical expression in the select list, e.g., following statement will also produce the same result as above but it will name the column marks*.35 as Term1 :

SELECT rollno, name, grade, section, marks*0.35 as Term1 From data
WHERE marks > 70
ORDER BY section ASC, Term1 DESC ;
Output
rollnonamegradesectionTerm1
101RuhaniAA26.8800
102GeorgeBA24.9200
108MeeraA+B34.0200
113GurnoorA+B32.7250
106ArsiyaA+B32.0600
103SimranAB28.4200
115RabiyaBB25.3750
112AdamBC25.9700

MySQL ORDER BY clause to sort data using a custom list


Sometimes, you have a column where you want to arrange data as per your own specified order.

For example, if there is column called project that stores the status of project made by the students. It can have possible values as Evaluated, Submitted, Pending, Assigned. If you want to arrange the result set on the basis of this project column as per this order : Evaluated, Pending, Submitted, Assigned.

For this, you need to used the FIELD function in ORDER BY clause as per this format :

SELECT
:
ORDER BY FIELD(<column name>, <values specifying order>) ;

The FIELD function internally maps the values-specifying-order to a list numeric values and then uses those numbers for sorting. You need not do anything, FIELD() does all this on its own.
To understand, let us consider the same data table with added project column with these values.

rollnonamemarksgradesectionproject
101Ruhani76.80AAPending
102George71.20BASubmitted
103Simran81.20ABEvaluated
104Ali61.20BCAssigned
105Kushal51.60CCEvaluated
106Arsiya91.60A+BSubmitted
107Raunaq32.50FBSubmitted
108Meera97.20A+BEvaluated
109Amaal57.20CBPending
111Simran66.00BAPending
112Adam74.20BCPending
113Gurnoor93.50A+BAssigned
115Rabiya72.50BBAssigned
117Rahil32.00FCSubmitted
118Neha59.50CAEvaluated

Now to order the above table as per the mentioned order above i.e., as per Project field having values in this order : ‘Evaluated’, ‘Pending’, ‘Submitted’, ‘Assigned’, you can write the query given below.

Query
SELECT * from data
ORDER BY FIELD(Project, 'Evaluated', 'Pending', 'Submitted', 'Assigned') ;
Output
rollnonamemarksgradesectionproject
118Neha59.50CAEvaluated
108Meera97.20A+BEvaluated
105Kushal51.60CCEvaluated
103Simran81.20ABEvaluated
101Ruhani76.80AAPending
112Adam74.20BCPending
111Simran66.00BAPending
109Amaal57.20CBPending
102George71.20BASubmitted
107Raunaq32.50FBSubmitted
106Arsiya91.60A+BSubmitted
117Rahil32.00FCSubmitted
113Gurnoor93.50A+BAssigned
115Rabiya72.50BBAssigned
104Ali61.20BCAssigned

Read More :

MySQL GROUP BY Clause with Examples

MySQL Functions with Examples

Aggregate Functions in MySQL

MySQL ORDER BY

Frequently Asked Questions

Q 1. How do I order by ascending in MySQL?

Ans.

The SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Q 2. How do I sort by alphabetical order in MySQL?

Ans. Introduction to MySQL ORDER BY clause

In this syntax, you specify the one or more columns which you want to sort after the ORDER BY clause. The ASC stands for ascending and the DESC stands for descending. You use ASC to sort the result set in ascending order and DESC to sort the result set in descending order.

Q 3. How do you use order by?

Ans.

The ORDER BY statement in sql is used to sort the fetched data in either ascending or descending according to one or more columns.

  1. By default ORDER BY sorts the data in ascending order.
  2. We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

Q 4. How do I count in MySQL?

Ans. MySQL count() function is used to returns the count of an expression.

MySQL Count() Function with HAVING and ORDER BY Clause

  1. mysql> SELECT emp_name, emp_age, COUNT(*) FROM employees.
  2. GROUP BY emp_age.
  3. HAVING COUNT(*)>=2.
  4. ORDER BY COUNT(*);

Q 5. How do you order two things in SQL?

Ans. After the ORDER BY keyword, add the name of the column by which you’d like to sort records first (in our example, salary). Then, after a comma, add the second column (in our example, last_name ). You can modify the sorting order (ascending or descending) separately for each column.

Q 6. How do I change the order of rows in MySQL?

Ans. An “ALTER TABLE ORDER BY” statement exist in the syntaxes accepted by MySQL. According to the documentation, this syntax: – only accept *one* column, as in “ALTER TABLE t ORDER BY col;” – is used to reorder physically the rows in a table, for optimizations.

Q 8. How do I sort a SQL query?

Ans. SQL ORDER BY Keyword

  1. ORDER BY. The ORDER BY command is used to sort the result set in ascending or descending order. …
  2. ASC. The ASC command is used to sort the data returned in ascending order. …
  3. DESC. The DESC command is used to sort the data returned in descending order.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *