Basic about ORDER BY Clause – using and example

This SQL tutorial explains how to use the SQL ORDER BY clause with syntax and examples.

Description

The SQL ORDER BY clause is used to type the records in the result set for a SELECT statement.

Syntax

The syntax for the ORDER BY clause in SQL is:

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

Parameters or Arguments

expressions The columns or calculations that you wish to retrieve. tables The tables that you desire to retrieve information from. There have to be at least one table listed in the FROM clause. WHERE conditions Optional. Conditions that must be met for the files to be selected. ASC Optional. ASC kinds the result set in ascending order with the aid of expression. This is the default behavior, if no modifier is provider. DESC Optional. DESC kinds the end result set in descending order through expression.

Note

If the ASC or DESC modifier is now not provided in the ORDER BY clause, the effects will be sorted by means of expression in ascending order. This is equal to ORDER BY expression ASC.

DDL/DML for Examples

If you choose to observe alongside with this tutorial, get the DDL to create the tables and the DML to populate the data. Then attempt the examples in your very own database!

Get DDL/DML

Example – Sorting Results in Ascending Order

To type your effects in ascending order, you can specify the ASC attribute. If no cost (ASC or DESC) is supplied after a area in the ORDER BY clause, the type order will default to ascending order. Let’s explore this further.

In this example, we have a desk referred to as clients with the following data:

customer_id last_name first_name favorite_website 4000 Jackson Joe techonthenet.com 5000 Smith Jane digminecraft.com 6000 Ferguson Samantha bigactivities.com 7000 Reynolds Allen checkyourmath.com 8000 Anderson Paige NULL 9000 Johnson Derek techonthenet.com

Enter the following SQL statement:

Try It

SELECT *
FROM customers
ORDER BY last_name;

There will be 6 records selected. These are the consequences that you should see:

customer_id last_name first_name favorite_website 8000 Anderson Paige NULL 6000 Ferguson Samantha bigactivities.com 4000 Jackson Joe techonthenet.com 9000 Johnson Derek techonthenet.com 7000 Reynolds Allen checkyourmath.com 5000 Smith Jane digminecraft.com

This example would return all documents from the customers sorted with the aid of the last_name discipline in ascending order and would be equal to the following SQL ORDER BY clause:

Try It

SELECT *
FROM customers
ORDER BY last_name ASC;

Most programmers miss the ASC attribute if sorting in ascending order.

Example – Sorting Results in descending order

When sorting your result set in descending order, you use the DESC attribute in your ORDER BY clause. Let’s take a closer look.

In this example, we have a table called suppliers with the following data:

supplier_id supplier_name town state 100 Microsoft Redmond Washington 200 Google Mountain View California 300 Oracle Redwood City California 400 Kimberly-Clark Irving Texas 500 Tyson Foods Springdale Arkansas 600 SC Johnson Racine Wisconsin 700 Dole Food Company Westlake Village California 800 Flowers Foods Thomasville Georgia 900 Electronic Arts Redwood City California

Enter the following SQL statement:

Try It

SELECT *
FROM suppliers
WHERE supplier_id > 400
ORDER BY supplier_id DESC;

There will be 5 records selected. These are the results that you have to see:

supplier_id supplier_name metropolis state 900 Electronic Arts Redwood City California 800 Flowers Foods Thomasville Georgia 700 Dole Food Company Westlake Village California 600 SC Johnson Racine Wisconsin 500 Tyson Foods Springdale Arkansas

This instance would sort the result set by using the supplier_id field in descending order.

Example – Sorting Results by relative position

You can additionally use the SQL ORDER BY clause to type by relative role in the end result set, the place the first field in the end result set is 1, the 2nd field is 2, the 0.33 subject is 3, and so on.

In this example, we have a table called products with the following data:

product_id product_name category_id 1 Pear 50 2 Banana 50 3 Orange 50 4 Apple 50 5 Bread 75 6 Sliced Ham 25 7 Kleenex NULL

Now enter the following SQL statement:

Try It

SELECT product_id, product_name
FROM products
WHERE product_name <> 'Bread'
ORDER BY 1 DESC;

There will be 6 information selected. These are the outcomes that you have to see:

product_id product_name 7 Kleenex 6 Sliced Ham 4 Apple 3 Orange 2 Banana 1 Pear

This example would type the consequences by way of the product_id subject in descending order, for the reason that the product_id discipline is in position #1 in the end result set and would be equivalent to the following SQL ORDER BY clause:

Try It

SELECT product_id, product_name
FROM products
WHERE product_name <> 'Bread'
ORDER BY product_id DESC;

Example – Using both ASC and DESC attributes

When sorting your end result set the use of the SQL ORDER BY clause, you can use the ASC and DESC attributes in a single SELECT statement.

In this example, let’s use the same merchandise table as the preceding example:

product_id product_name category_id 1 Pear 50 2 Banana 50 3 Orange 50 4 Apple 50 5 Bread 75 6 Sliced Ham 25 7 Kleenex NULL

Now enter the following SQL statement:

Try It

SELECT *
FROM products
WHERE product_id <> 7
ORDER BY category_id DESC, product_name ASC;

There will be 6 documents selected. These are the effects that you see:

product_id product_name category_id 5 Bread 75 4 Apple 50 2 Banana 50 3 Orange 50 1 Pear 50 6 Sliced Ham 25

This instance would return the files sorted by means of the category_id subject in descending order, with a secondary type by means of product_name in ascending order.