This page in other versions: 9.0 / 9.1 / 9.2 / 9.3  |  Development versions: devel / 9.4  |  Unsupported versions: 7.1 / 7.2 / 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3 / 8.4

7.5. Sorting Rows

After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

The ORDER BY clause specifies the sort order:

SELECT select_list
    FROM table_expression
    ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...]

column1, etc., refer to select list columns. These can be either the output name of a column (see Section 7.3.2) or the number of a column. Some examples:

SELECT a, b FROM table1 ORDER BY a;
SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, sum(b) FROM table1 GROUP BY a ORDER BY 1;

As an extension to the SQL standard, PostgreSQL also allows ordering by arbitrary expressions:

SELECT a, b FROM table1 ORDER BY a + b;

References to column names of the FROM clause that are not present in the select list are also allowed:

SELECT a FROM table1 ORDER BY b;

But these extensions do not work in queries involving UNION, INTERSECT, or EXCEPT, and are not portable to other SQL databases.

Each column specification may be followed by an optional ASC or DESC to set the sort direction to ascending or descending. ASC order is the default. Ascending order puts smaller values first, where "smaller" is defined in terms of the < operator. Similarly, descending order is determined with the > operator. [1]

If more than one sort column is specified, the later entries are used to sort rows that are equal under the order imposed by the earlier sort columns.

Notes

[1]

Actually, PostgreSQL uses the default B-tree operator class for the column's data type to determine the sort ordering for ASC and DESC. Conventionally, data types will be set up so that the < and > operators correspond to this sort ordering, but a user-defined data type's designer could choose to do something different.

Comments


Nov. 10, 2005, 10:39 p.m.

I had a hard time getting a "natural" sort-order. For instance, I wanted the list returned in the following order:

SELECT room_number FROM rooms ORDER BY room_number;
room_number
-----------
8
9
9a
10

So I wrote the btrsort() function (see below).

Now, I'll do:
SELECT room_number
FROM (SELECT room_number, btrsort(room_number) AS room_sort
FROM rooms
ORDER BY room_sort) AS foo

And lo:
room_number
-----------
8
9
9a
10

Here's those functions.

CREATE FUNCTION btrsort_nextunit(text) RETURNS text AS $$
SELECT CASE WHEN $1 ~ '[^0-9]+' THEN
COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM '[^0-9]+'))+1 ),
'' )
ELSE
COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM '[0-9]+'))+1 ),
'' )
END
$$ LANGUAGE SQL;

CREATE FUNCTION btrsort(text, integer) RETURNS text AS $$
SELECT CASE WHEN $2-1&gt;0
THEN
RPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM '^[^0-9]+'), ''), 1, 12), 12, ' ') ||
LPAD(SUBSTR(COALESCE(SUBSTRING(btrsort_nextunit($1) FROM '^[0-9]+'), ''), 1, 12), 12, 0) || btrsort(btrsort_nextunit(btrsort_nextunit($1)), $2-1)
ELSE
RPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM '^[^0-9]+'), ''), 1, 12), 12, ' ') ||
LPAD(SUBSTR(COALESCE(SUBSTRING(btrsort_nextunit($1) FROM '^[0-9]+'), ''), 1, 12), 12, 0)
END
;

$$ LANGUAGE SQL;

CREATE FUNCTION btrsort(text) RETURNS text AS $$
SELECT btrsort($1, 10);
$$ LANGUAGE SQL;

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group