{"id":156,"date":"2019-10-05T08:53:54","date_gmt":"2019-10-05T01:53:54","guid":{"rendered":"https:\/\/mariadbtutorial.com\/?page_id=156"},"modified":"2020-04-11T23:24:13","modified_gmt":"2020-04-11T16:24:13","slug":"mariadb-order-by","status":"publish","type":"page","link":"https:\/\/www.mariadbtutorial.com\/mariadb-basics\/mariadb-order-by\/","title":{"rendered":"MariaDB Order By"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MariaDB order by<\/code> clause to sort the result of a query.<\/p>\n\n\n\n

Introduction to MariaDB order by clause<\/h2>\n\n\n\n

The select<\/a><\/code> statement returns a result set whose rows are not in any specific order. To sort the rows of a result set by values in one or more columns, you use the order by<\/code> clause.<\/p>\n\n\n\n

The following shows the syntax of the select<\/code> statement with an order by<\/code> clause:<\/p>\n\n\n

select<\/span> \n    select_list\nfrom<\/span> \n    table_name\norder<\/span> by<\/span>\n    sort_expression1 [asc<\/span> | desc<\/span>],\n    sort_expression2 [asc<\/span> | desc<\/span>],\n    ...;    \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The order by<\/code> clause appears at the end of a select<\/code> statement.<\/p>\n\n\n\n
In this syntax:<\/p>\n\n\n\n
  • First, specify one or more sort expression sort_expression1<\/code>, sort_expression2<\/code>, … which can be columns or expressions by which you want to order.<\/li>
  • Then, use asc<\/code> or desc<\/code> to sort the rows in the result set in ascending order (asc<\/code>) or descending order (desc<\/code>).<\/li><\/ul>\n\n\n\n
    The order by<\/code> clause uses asc<\/code> by default.<\/p>\n\n\n\n
    MariaDB evaluates clauses of the select<\/code> statement in the following orders: from<\/code>, select<\/code> and order by<\/code>:<\/p>\n\n\n\n
    \"mariadb<\/figure>\n\n\n\n
    MariaDB order by clause examples<\/h2>\n\n\n\n
    We’ll use the countries<\/code> table from the sample database for the demonstration.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    A) Using MariaDB order by clause to sort rows by one column example<\/h3>\n\n\n\n
    The following select<\/code> statement returns the names and areas of countries:<\/p>\n\n\n
    select<\/span> \n    name<\/span>, \n    area\nfrom<\/span> \n    countries\norder<\/span> by<\/span> name<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"mariadb<\/figure>\n\n\n\n
    As you can see, the order of rows in the result set is not specified.<\/p>\n\n\n\n
    The following select statement uses the order by<\/code> clause to sort countries<\/code> by name:<\/p>\n\n\n
    select<\/span> \n    name<\/span>, \n    area\nfrom<\/span> \n    countries\norder<\/span> by<\/span> \n    name<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"mariadb<\/figure>\n\n\n\n
    The order by<\/code> clause sorted the countries by name in ascending order because it uses asc<\/code> by default.<\/p>\n\n\n\n
    To sort the countries<\/code> by name in descending order, you use the desc<\/code> option:<\/p>\n\n\n
    select<\/span> \n    name<\/span>, \n    area\nfrom<\/span> \n    countries\norder<\/span> by<\/span> \n    name<\/span> desc<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"mariadb<\/figure>\n\n\n\n
    B) Using MariaDB order by clause to sort a result set by two columns example<\/h3>\n\n\n\n
    The following statement uses the order by<\/code> clause to sort countries<\/code> by name and region_id<\/code>:<\/p>\n\n\n
    select<\/span> \n    name<\/span>, \n    region_id\nfrom<\/span> \n    countries\norder<\/span> by<\/span> \n    region_id, \n    name<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"mariadb<\/figure>\n\n\n\n
    In this statement, the order by<\/code> sorts the rows in the following sequence:<\/p>\n\n\n\n
    • First, sort the countries<\/code> by values in region_id<\/code> column from low to high.<\/li>
    • Then, sort the sorted countries by values in the name<\/code> column alphabetically.<\/li><\/ul>\n\n\n\n
      MariaDB order by clause with null values<\/h2>\n\n\n\n
      The null<\/code> is a special value in MariaDB. The null<\/code> indicates that a value is missing or not applicable.<\/p>\n\n\n\n
      MariaDB treats null<\/code> as the lowest value so when you sort by a column that has null values, null values appear first in the result set.<\/p>\n\n\n\n
      The following statement uses the order by<\/code> clause to sort countries by national days:<\/p>\n\n\n
      select<\/span> \n    name<\/span>, \n    national_day\nfrom<\/span> \n    countries\norder<\/span> by<\/span> \n    national_day;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      \"mariadb<\/figure>\n\n\n\n
      If you use the desc<\/code> in this case, the null<\/code> values will appear last in the result set:<\/p>\n\n\n
      select<\/span> \n    name<\/span>, \n    national_day\nfrom<\/span> \n    countries\norder<\/span> by<\/span> \n    national_day desc<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      \"mariadb<\/figure>\n\n\n\n
      In this tutorial, you have learned how to use the MariaDB order by<\/code> clause to sort a result set by values in one or more columns in ascending or descending order.<\/p>\n
      \n\t
      \n\t\t
      Was this tutorial helpful ?<\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t