Product Documentation

Appearance

ORDER BY Clause ​

Use the ORDER BY clause to define how the final result set for a query (or an intermediate result set for a subquery) is sorted and displayed.

[ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] [ COLLATE collation ] ]
expression

One of the following:

  • Column name
  • Column expression
  • Column alias
  • Column ordinal (a number that refers to the position of the column or expression in the select list)
ASC | DESC

ASC (ascending) is the default order. DESC is often used for ranking numeric results from high to low. Each of these keywords may follow any ORDER BY expression.

NULLS FIRST | LAST

NULL values can be sorted first or last for any ORDER BY expression. NULLS LAST is the default when ascending order is in effect, and NULLS FIRST is the default when descending order is in effect.

COLLATE collation

The COLLATE clause is supported in UTF8 databases only. This clause defines the sort order based on a specific predefined collation. You cannot create collations in a Yellowbrick database. See Supported Collations (UTF8 Databases Only).

Usage Notes ​

  • ORDER BY expressions must appear in the select list when DISTINCT is used in the select list.
  • Parameters defined in prepared statements cannot be used in the ORDER BY clause.
  • Whole-row references are not supported in the ORDER BY clause.
  • By default, the C collation order is used for both LATIN9 databases and UTF8 databases. However, UTF8 databases support the COLLATE clause. See Supported Collations (UTF8 Databases Only).

Examples ​

Assume that the select list looks like this:

select winners as club, season_name as season

For this select list, all of the following are valid ORDER BY clauses:

order by winners nulls first, season_name desc;
order by club nulls first, season desc;
order by 1 nulls first, 2 desc;

Here is a complete query with an ORDER BY clause:

premdb=# select * from season 
order by winners nulls first, seasonid;
 seasonid | season_name | numteams |      winners      
----------+-------------+----------+-------------------
      25 | 2016-2017   |       20 | 
       6 | 1997-1998   |       20 | Arsenal
      10 | 2001-2002   |       20 | Arsenal
      12 | 2003-2004   |       20 | Arsenal
       3 | 1994-1995   |       22 | Blackburn Rovers
      13 | 2004-2005   |       20 | Chelsea
      14 | 2005-2006   |       20 | Chelsea
      18 | 2009-2010   |       20 | Chelsea
      23 | 2014-2015   |       20 | Chelsea
...

This example demonstrates the restriction for SELECT DISTINCT queries with ORDER BY clauses:

premdb=# select distinct season_name, winners from season 
order by winners nulls first, seasonid;
ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ... winners from season order by winners nulls first, seasonid;

This example demonstrates the restriction on whole-row references in the ORDER BY clause:

premdb=# select team.* from team order by team.*;
ERROR:  Whole row references are not allowed: team.*
LINE 1: select team.* from team order by team.*;

The following example runs in a UTF8 database and contains a COLLATE clause:

premdb_utf8=# select * from chinese_names order by name collate "zh_CN";
         name          
------------------------
 利物浦
 曼彻斯特城
 曼联队
 切尔西
 托特纳姆热刺足球俱乐部
(5 rows)

Supported Collations (UTF8 Databases Only) ​

UTF8 databases support the following collations in the ORDER BY...COLLATE clause. The default C collation is also supported.

ar_AE
ar_BH
ar_DZ
ar_EG
ar_IQ
ar_JO
ar_KW
ar_LB
ar_LY
ar_MA
ar_OM
ar_QA
ar_SA 
ar_SD 
ar_SY 
ar_TN 
ar_YE 
be_BY 
bg_BG 
ca_ES 
cs_CZ 
da_DK 
de_AT 
de_CH
de_DE 
de_LU 
el_CY 
el_GR 
en_AU 
en_CA 
en_GB 
en_IE 
en_IN 
en_NZ 
en_PH 
en_SG
en_US 
en_ZA 
es_AR 
es_BO 
es_CL 
es_CO 
es_CR 
es_DO 
es_EC 
es_ES 
es_GT 
es_HN
es_MX 
es_NI 
es_PA 
es_PE 
es_PR 
es_PY 
es_SV 
es_US 
es_UY 
es_VE 
et_EE 
fi_FI
fr_BE 
fr_CA 
fr_CH 
fr_FR 
fr_LU 
ga_IE 
hi_IN 
hr_HR 
hu_HU 
is_IS 
it_CH 
it_IT
iw_IL 
ja_JP 
ko_KR 
lt_LT 
lv_LV 
mk_MK 
ms_MY 
mt_MT 
nl_BE 
nl_NL 
pl_PL 
pt_BR
pt_PT 
ro_RO 
ru_RU 
sk_SK 
sl_SI 
sq_AL 
sr_ME 
sr_RS 
sv_SE 
th_TH 
tr_TR 
uk_UA
vi_VN 
zh_CN 
zh_HK 
zh_SG 
zh_TW

Parent topic:SELECT