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).