Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Sorting Rows with ORDER BY

As described in Chapter 3, row data is not stored in a consistent order within tables. In fact, an identical query executed twice is in no way guaranteed to return the rows in the same order each time. As order is commonly an important part of retrieving data for database-dependent applications, use the ORDER BY clause to allow flexible sorting of your result set.

The ORDER BY clause accepts as its parameters a list of comma-delimited column names (or expressions upon columns), which are used as sorting criteria. For each sort criteria, you may optionally apply either the ASC, DESC, or USING keywords to control the type of sorting employed:

ASC

Causes the rows to sort by the related criteria in an ascending fashion (e.g., numbers will be sorted lowest to highest, text will be sorted alphabetically from a to z). ASC is equivalent to specifying USING <. Since it is the default behavior, specifying ASC is only useful for explicit readability.

DESC

Causes the rows to sort by the related criteria in a descending fashion (e.g., numbers will be sorted highest to lowest, text will be sorted alphabetically from z to a). DESC is equivalent to specifying USING >.

USING operator

Allows the specification of the operator operator to be used to compare each column for precedence. This can be particularly useful for custom operators.

Example 4-42 demonstrates the use of the ORDER BY clause on the editions table. It specifies the publication column as the source of values to sort by, and explicitly declares the ordering method as an ascending (ASC) sort.

Example 4-42. Using ORDER BY

booktown=# 
SELECT isbn, edition, publication

booktown-# 
       FROM editions

booktown-# 
       ORDER BY publication ASC;

    isbn    | edition | publication
------------+---------+-------------
 0760720002 |       1 | 1868-01-01
 0679803335 |       1 | 1922-01-01
 0694003611 |       1 | 1947-03-04
 0394800753 |       1 | 1949-03-01
 0394900014 |       1 | 1957-01-01
 039480001X |       1 | 1957-03-01
 0823015505 |       1 | 1958-01-01
 0451160916 |       1 | 1981-08-01
 0590445065 |       1 | 1987-03-01
 0385121679 |       2 | 1993-10-01
 1885418035 |       1 | 1995-03-28
 0441172717 |       2 | 1998-09-01
 0929605942 |       2 | 1998-12-01
 044100590X |       3 | 1999-10-01
 0451198492 |       3 | 1999-10-01
 0451457994 |       3 | 2000-09-12
 0596000855 |       2 | 2001-03-01
(17 rows)

As you can see in the result set from Example 4-42, the rows return in ascending order, from the oldest date to the newest. It should be noted that even columns and expressions that do not appear in the target list of the SELECT statement may be used to sort the retrieved rows. Furthermore, aggregate functions and expressions are allowed by the ORDER BY clause if the query involves aggregation. The ability to sort by such a wide scope of sources thus allows for a great deal of flexibility in ordering results from a variety of query approaches.

Warning

If a column alias in the result set has the same name as a literal column in an input source from which it is drawing rows, and it is used in the ORDER BY clause, PostgreSQL will assume that it is a reference to the named column in the result set, not the column in the source set. This is an accepted inconsistency compared against the default behavior of the GROUP BY clause, as specified by the SQL92 standard.

When specifying multiple expressions to sort by, the result set will be ordered by the first criteria (from left to right), and will only process subsequent sorting criteria if the first condition's sort is inconclusive. For example, consider the sorting performed in Example 4-43.

Example 4-43. Using ORDER BY with multiple expressions

booktown=# 
SELECT edition, publication

booktown-# 
       FROM editions

booktown-# 
       ORDER BY edition ASC,

booktown-# 
                publication DESC;

 edition | publication
---------+-------------
       1 | 1995-03-28
       1 | 1987-03-01
       1 | 1981-08-01
       1 | 1958-01-01
       1 | 1957-03-01
       1 | 1957-01-01
       1 | 1949-03-01
       1 | 1947-03-04
       1 | 1922-01-01
       1 | 1868-01-01
       2 | 2001-03-01
       2 | 1998-12-01
       2 | 1998-09-01
       2 | 1993-10-01
       3 | 2000-09-12
       3 | 1999-10-01
       3 | 1999-10-01
(17 rows)

The query in Example 4-43 selects the numeric edition and publication date of each book from the editions table. The ORDER BY clause then specifies two columns to sort by: edition, in ascending order, and publication, in descending order.

As you can see in the result set for Example 4-43, each row is first sorted by edition, proceeding from the lower editions to the higher editions. Subsequently, wherever the editions are identical, the publication date is used to then sort again, from the most recent publication date to the least recent.

Sorting is extremely relevant when using the DISTINCT keyword, as discussed in the Section called Removing Duplicate Rows with DISTINCT ." If you are only interested in seeing the most recently published copy of each edition in the editions table, the ORDER BY and DISTINCT clauses can be combined to achieve an effect somewhat similar to the GROUP BY clause, as shown in Example 4-44.

Example 4-44. Using DISTINCT with ORDER BY

booktown=# 
SELECT DISTINCT ON (edition)

booktown-# 
       edition, publication

booktown-# 
       FROM editions

booktown-# 
       ORDER BY edition ASC,

booktown-# 
                publication DESC;

 edition | publication
---------+-------------
       1 | 1995-03-28
       2 | 2001-03-01
       3 | 2000-09-12
(3 rows)

booktown=# 
SELECT edition, max(publication)

booktown-# 
       FROM editions

booktown-# 
       GROUP BY edition;

 edition |    max
---------+------------
       1 | 1995-03-28
       2 | 2001-03-01
       3 | 2000-09-12
(3 rows)

Since the ORDER BY occurring before the DISTINCT clause eliminates duplicate rows, the net effect can be very similar to using the max() or min() with a GROUP BY clause. This technique can sometimes be more efficient, depending on the complexity of the aggregation and sorting involved.

Note: While never strictly necessary, PostgreSQL can accept integer constants as expressions in the ORDER BY clause, instead of column names or expressions. Such a constant will be interpreted as representing the column that is at the numbered position in the target list, from left to right, starting at 1 (e.g., ORDER BY 1 ASC references the first column in the result set).

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire