Home arrow Oracle arrow Sorting Database Columns With the SELECT Statement

Sorting Database Columns With the SELECT Statement

In this conclusion to a multi-part series on using the SELECT statement in an Oracle database, you'll learn how to sort a result set by an alias, an expression, or a column number. This article is excerpted from chapter three of the book Murach's Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).

TABLE OF CONTENTS:
  1. Sorting Database Columns With the SELECT Statement
  2. Perspective
By: Murach Publishing
Rating: starstarstarstarstar / 0
September 08, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

How to sort a result set by an alias, an expression, or a column number

Figure 3-18 presents three more techniques that you can use to specify sort columns. First, you can use a column alias thatís defined in the SELECT clause. The first SELECT statement in this figure, for example, sorts by a column named address, which is an alias for the concatenation of the vendor_city, vendor_state, and vendor_zip_code columns. Within the address column, the result set is sorted by the vendor_name column.

You can also use an arithmetic or string expression in the ORDER BY clause, as illustrated by the second example in this figure. Here, the expression consists of the vendor_contact_last_name column concatenated with the vendor_contact_first_name column. Here, neither of these columns is included in the SELECT clause.

The last example in this figure shows how you can use column numbers to specify a sort order. To use this technique, you code the number that corresponds to the column of the result set, where 1 is the first column, 2 is the second column, and so on. In this example, the ORDER BY clause sorts the result set by the second column, which contains the concatenated address, then by the first column, which contains the vendor name. The result set returned by this statement is the same as the result set returned by the first statement.

Notice, however, that the statement that uses column numbers is more difficult to read because you have to look at the SELECT clause to see what columns the numbers refer to. In addition, if you add or remove columns from the SELECT clause, you may also have to change the ORDER BY clause to reflect the new column positions. As a result, we donít recommend this coding technique.

An ORDER BY clause that uses an alias

SELECT vendor_name,
vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS address
FROM vendors
ORDER BY address, vendor_name

An ORDER BY clause that uses an expression

SELECT vendor_name,
vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS address
FROM vendors
ORDER BY vendor_contact_last_name || vendor_contact_first_name

An ORDER BY clause that uses column positions

SELECT vendor_name,
vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS address
FROM vendors
ORDER BY 2, 1

Description

  1. The ORDER BY clause can include a column alias thatís specified in the SELECT clause.
  2. The ORDER BY clause can include any valid expression. The expression can refer to any column in the base table, even if it isnít included in the result set.
  3. The ORDER BY clause can use numbers to specify the columns to use for sorting. In that case, 1 represents the first column in the result set, 2 represents the second column, and so on.

--------------------------------------------Figure 3-18 How to sort a result set by an alias, an expression, or a column number



 
 
>>> More Oracle Articles          >>> More By Murach Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: