How to sort query results in oracle

In Oracle, you can use the "SELECT" statement and the "ORDER BY" clause to sort the query. You can sort the query result set of one or more columns in ascending or descending order. The syntax "SELECT field name" FROM data table name ORDER BY field name [ASC|DESC]".

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

In Oracle, you can use the "SELECT" statement and the "ORDER BY" clause to sort queries.

In Oracle, row data records are stored in the table in a non-specified order, regardless of the order in which the rows are inserted into the database. To query row records by column in ascending or descending order, you must explicitly instruct the Oracle database how to sort.

For example, you might want to list all customers alphabetically by name, or display all customers in order from lowest to highest credit limit.

To sort the queried data, add the ORDER BY clause to the SELECT statement as follows.

ORDER BY clause can sort the result set of one or more columns in ascending or descending order.

Syntax:

SELECT
    字段名,
FROM
    table_name
ORDER BY
    字段名 [ASC | DESC] [NULLS FIRST | NULLS LAST]

To sort the result set by a column, you can list the column after the ORDER BY clause.

It is a sorting order according to the column name, which can be:

• ASC means sorting in ascending order

• DESC means sorting in descending order

By default, the ORDER BY clause is used whether or not ASC is specified. Both sort the rows in ascending order. If you want to sort the rows in descending order, use DESC explicitly.

NULLS FIRST Place NULL values ​​before non-NULL values, NULLS LAST before non-## Place the NULL value after the #NULL value.

ORDER BY clause can sort data by multiple columns, and each column may have a different sort order.

Note that the

ORDER BY clause is always the last clause in a SELECT statement.

Example of Oracle ORDER BY clause

The following will use the

customers table in the sample database for demonstration.

The following statement retrieves the customer's from the customers (

customers) table: name, address and credit limit:

SELECT name, address, credit_limit FROM customers;

Execute the above query statement and get the following results-

As you can see in the screenshot, row records The order is unspecified.

1. Example of sorting rows by column

#To sort customer names alphabetically in ascending order, use the following statement:

SELECT name,address,credit_limit
FROM customers
ORDER BY name ASC;

Execute the above example code and get the following results -

ASC instructs Oracle to sort the rows in ascending order. But ASC is optional, and if omitted, by default, the ORDER BY clause sorts the rows by the specified column in ascending order.

Therefore, the following expression:

ORDER BY name ASC

is equivalent to -

ORDER BY name

To order customer names in descending alphabetical order, ORDER BY

Explicitly use DESC after the column name in the clause, as shown below: <pre class="brush:sql;toolbar:false;">SELECT name, address, credit_limit FROM customers ORDER BY name DESC;</pre>Execute the above query statement and get the following results-

2. Example of sorting rows by multiple columns To sort multiple columns, you can separate them with commas

ORDER BY

each column in the sentence. See the following Contacts (

contacts

) table in the sample database. For example, to sort by

first_name

in ascending order and the last_name column in descending order, use the following statement: <pre class="brush:sql;toolbar:false;">SELECT first_name, last_name FROM contacts ORDER BY first_name, last_name DESC;</pre>Execute From the above sample code, you can see the following results -

##As you can see from the screenshot above, contact_id

is

91 and 311The values ​​of first_name of these two records are the same, and the values ​​of last_name are arranged in descending order. In this example, Oracle first sorts the rows by first_name

in ascending order to create the initial result set. Oracle then sorts the initial result set on

last_name in descending order. See results similar to the following:

In the above result:

• 首先，按first_name升序排序。
• 其次，如果两个名字相同，按last_name降序排列，如Daniel Glass和Daniel Costner，Dianne Sen和Dianne Derek，Doretha Tyler和Dorotha Wong。

3、按列位置排序行示例

不需要指定用于排序数据的列名称。如果您愿意，可以使用ORDER BY子句中列的位置。

请参考下语句 -

SELECT name, credit_limit,address
FROM customers
ORDER BY 2 DESC, 1;

在这个例子中，name列的位置是1，credit_limit列的位置是2。相当于以下查询语句 -

SELECT name, credit_limit,address
FROM customers
ORDER BY credit_limit DESC, name;

在ORDER BY子句中，使用这些列位置来指示Oracle对行进行排序。

4、用NULL值排序行的示例

请参阅示例数据库中的以下地区(locations)表：

以下语句按城市(city)和州(state)检索位置并对其进行排序：

SELECT country_id, city, state
FROM locations 
ORDER BY state DESC;

执行上面查询语句，得到以下结果 -

state列有NULL值，这意味着state数据与某些城市(例如：Beijing, Hiroshima 和 London)无关。

当使用非NULL值对混合NULL进行排序时，Oracle允许指定哪个应该首先出现。

例如，以下语句按升序对state列进行排序，并首先将NULL值放置在前面。

SELECT country_id, city, state
FROM
    locations
ORDER BY
    state ASC NULLS FIRST;

要放置NULL值在后面，可以使用NULLS LAST，如下面的语句所示：

SELECT country_id, city, state
FROM
    locations
ORDER BY
    state ASC NULLS LAST;

执行上面示例查询语句，得到以下结果：

5、按函数或表达式排序数据

ORDER BY子句可在一列上应用一个函数，例如字符串函数，数学函数等，并按函数的结果对数据进行排序。

例如，以下语句使用ORDER BY子句中的UPPER()函数来区分客户名称的大小写：

SELECT customer_id, name
FROM customers
ORDER BY UPPER( name );

执行上面查询语句，得到以下结果 -

推荐教程：《Oracle教程》

The above is the detailed content of How to sort query results in oracle. For more information, please follow other related articles on the PHP Chinese website!