16 - MySQL Sorting Results

MySQL provides the way to sort the result of any query in the ascending and descending order. MySQL provides a keyword “ORDER BY” to sort the data in the result set of the Select query. By default the ORDER BY keyword sorts the data in the ascending order. There are keywords ASC and DESC used with the ORDER BY clause for sorting the data in ascending and descending order respectively.

The basic query to sort the result set:

SELECT column1, column2, column3,…………., column from tableName ORDER BY columnM;

Here tableName is the table on which SELECT command is executes and columnM is the column name by which you want to sort the result.

For example:

We have a data of the company names along with their address and price of some server.

ID

Company

Address

Amount

1

HCL

NY

$7000

2

Apple

Chicago

$2600

3

IBM

INDIA

$3800

4

Google

Singapore

$15000

We want to select all the records from the above table with the order of highest amount first. The select query for the same is as:
SELECT ID, Company, Address, Amount from company ORDER BY AMOUNT;
OR
SELECT ID, Company, Address, Amount from company ORDER BY AMOUNT ASC;

The result of above query is:

ID

Company

Address

Amount

4

Google

Singapore

$15000

1

HCL

NY

$7000

3

IBM

INDIA

$3800

2

Apple

Chicago

$2600

In the same way if you want to select the result in the descending order then use the keyword DESC with ORDER BY clause. For example if we want to select all data from company table in descending order of the company names.

      SELECT ID, Company, Address, Amount from company ORDER BY company desc;

ID

Company

Address

Amount

2

Apple

Chicago

$2600

4

Google

Singapore

$15000

1

HCL

NY

$7000

3

IBM

INDIA

$3800

NOTE: In order to get good performance in sorting result set, you should have apply index on the column on which you apply sorting or ORDER BY clause. In the above example you should apply index on Company name to sort descending.

You can also use ORDER BY or sorting for one or more columns, the basic syntax for the same is as:

      SELECT column1, column2, .. , columnN FROM tableNameORDER BY column1, column2;

For example we have the data of company as:

ID

Company

Address

Amount

2

Apple

Chicago

$2600

4

Google

Singapore

$15000

1

HCL

Chicago

$7000

3

IBM

INDIA

$3800

Here the Address Chicago is same for two companies and we need result set data in order of Address and then Company name. So the query will be:

SELECT ID, Company, Address, Amount from company ORDER BY Address, company;

The resultant of this query is:

ID

Company

Address

Amount

2

Apple

Chicago

$2600

1

HCL

Chicago

$7000

3

IBM

INDIA

$3800

4

Google

Singapore

$15000

Like us on Facebook

Wideskills