SQL ORDER BY: Sorting the Result Set based on One or More Columns about:reader?url=https://www.sqltutorial.

org/sql-order-by/

sqltutorial.org

SQL ORDER BY: Sorting the Result

Set based on One or More Columns
4-5 menit

Summary: This tutorial shows you how to use the SQL ORDER
BY clause to sort the result set based on specified criteria in
ascending or descending orders.

Introduction to SQL ORDER BY clause

When you use the SELECT statement to query data from a table,
the order which rows appear in the result set may not be what you
expected.

In some cases, the rows that appear in the result set are in the
order that they are stored in the table physically. However, in case
the query optimizer uses an index to process the query, the rows
will appear as they are stored in the index key order. For this
reason, the order of rows in the result set is undetermined or
unpredictable.

The query optimizer is a built-in software component in the

database system that determines the most efficient way for an
SQL statement to query the requested data.

To specify exactly the order of rows in the result set, you add use
an ORDER BY clause in the SELECT statement as follows:

1 dari 8 27/02/2021 08.12

SQL ORDER BY: Sorting the Result Set based on One or More Columns about:reader?url=https://www.sqltutorial.org/sql-order-by/

SELECT
column1, column2
FROM
table_name
ORDER BY column1 ASC ,
column2 DESC;

In this syntax, the ORDER BY clause appears after the FROM

clause. In case the SELECT statement contains a WHERE clause,
the ORDER BY clause must appear after the WHERE clause.

To sort the result set, you specify the column in which you want to
sort and the kind of the sort order:

Ascending ( ASC)

Descending ( DESC)

If you don’t specify the sort order, the database system typically
sorts the result set in ascending order ( ASC) by default.

When you include more than one column in the ORDER BY clause,
the database system first sorts the result set based on the first
column and then sort the sorted result set based on the second
column, and so on.

SQL ORDER BY clause examples

We will use the employees table in the sample database for the
demonstration.

2 dari 8 27/02/2021 08.12

SQL ORDER BY: Sorting the Result Set based on One or More Columns about:reader?url=https://www.sqltutorial.org/sql-order-by/

1) Using SQL ORDER BY clause to sort values in one column

example

The following statement retrieves the employee id, first name, last
name, hire date, and salary from the employees table:

SELECT
employee_id,
first_name,
last_name,
hire_date,
salary
FROM
employees;

See it in action

It seems that the rows appear as they are stored in the

employees table. To sort employees by first names
alphabetically, you add an ORDER BY clause to query as follows:

3 dari 8 27/02/2021 08.12

SQL ORDER BY: Sorting the Result Set based on One or More Columns about:reader?url=https://www.sqltutorial.org/sql-order-by/

SELECT
employee_id,
first_name,
last_name,
hire_date,
salary
FROM
employees
ORDER BY
first_name;

See it in action

The result set now is sorted by the first_name column.

2) Using SQL ORDER BY clause to sort values in multiple

columns example

To sort by the employees by the first name in ascending order and

the last name in descending order, you use the following
statement:

SELECT
employee_id,
first_name,
last_name,

4 dari 8 27/02/2021 08.12

SQL ORDER BY: Sorting the Result Set based on One or More Columns about:reader?url=https://www.sqltutorial.org/sql-order-by/

hire_date,
salary
FROM
employees
ORDER BY
first_name,
last_name DESC;

See it in action

First, the database system sorts the result set by the first name in
ascending order, then it sorts the sorted result set by the last name
in descending order. Notice the change in position of two
employees: Alexander Khoo and Alexander Hunold

3) Using SQL ORDER BY clause to sort values in a numeric

column example

SQL allows you to sort data alphabetically as shown in the

previous example and also sort data numerically. For example, the
following statement selects employee data and sorts the result set
by salary in the descending order:

SELECT
employee_id,
first_name,

5 dari 8 27/02/2021 08.12

SQL ORDER BY: Sorting the Result Set based on One or More Columns about:reader?url=https://www.sqltutorial.org/sql-order-by/

last_name,
hire_date,
salary
FROM
employees
ORDER BY
salary DESC;

See it in action

4) Using SQL ORDER BY to sort dates example

Besides the character and numeric, SQL allows you to sort the
result set by date. The following statement sorts the employees by
values in the hire_date column in the ascending order.

SELECT
employee_id,
first_name,
last_name,
hire_date,
salary
FROM
employees
ORDER BY
hire_date;

6 dari 8 27/02/2021 08.12

SQL ORDER BY: Sorting the Result Set based on One or More Columns about:reader?url=https://www.sqltutorial.org/sql-order-by/

See it in action

To view the latest employees who have just joined the company,
you sort the employees by the hire dates in the descending order
as shown in the following statement:

SELECT
employee_id,
first_name,
last_name,
hire_date,
salary
FROM
employees
ORDER BY
hire_date DESC;

See it in action

In this tutorial, you have learned how to use the SQL ORDER BY

7 dari 8 27/02/2021 08.12

SQL ORDER BY: Sorting the Result Set based on One or More Columns about:reader?url=https://www.sqltutorial.org/sql-order-by/

clause to sort the result set based on one or more columns in the
ascending or descending order.

Was this tutorial helpful ?

YesNo

8 dari 8 27/02/2021 08.12