5 ms·

That is not correct. The parent has it right. The output is in fact sorted by the second field; it is not random. > Each expression can be the name or ordinal

That is not correct. The parent has it right. The output is in fact sorted by the second field; it is not random. > Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values. > The ordinal number refers to the ordinal (left-to-right) position of the output column. This feature makes it possible to define an ordering on the basis of a column that does not have a unique name. This is never absolutely necessary because it is always possible to assign a name to an output column using the AS clause. (emphasis mine) https://www.postgresql.org/docs/current/sql-select.html#SQL-ORDERBY https://www.postgresql.org/docs/current/sql-select.html#SQL-...

Areading314 6y ago
Interesting -- didn't know it did this
- MaxGabriel 6y ago
  One way I've seen it used is when you need to order by all or nearly all of the columns in a select statement. Sometimes select a.1, a.2, a.3, ... a.10 GROUP BY 1,2,3,4,5,6,7,8,9,10 conveys the intent more clearly than listing out all the column names
  - sk5t 6y ago
    It is also very helpful to avoid re-typing expressions. select a || 'baz', avg(b) from foo group by 1 order by 2;
grecy 6y ago
could '2' be a column name? ... meaning 'order by 2' would order it by the values in the column named '2', but which wasn't selected.
- benesch 6y ago
  In PostgreSQL, the grammar does not permit unquoted identifiers to start with a digit. So this is a syntax error: SELECT 'val' AS 2 Instead you must escape the identifier with double quotes: SELECT 'val' AS "2" So there is no ambiguity on that front in the ORDER BY clause. Either you have `ORDER BY 2`, which means order by the second column, or you have `ORDER BY "2"`, which means order by the column named "2".
adamzochowski 6y ago
Some SQL Servers, like MS-SQL have been slowly going into banning order by a number / constant expression, and flagging column numbers as error prone constructs. https://www.sql-server-performance.com/error408/ https://www.sql-server-performance.com/error408/

Areading314 6y ago

Interesting -- didn't know it did this

MaxGabriel 6y ago
One way I've seen it used is when you need to order by all or nearly all of the columns in a select statement. Sometimes select a.1, a.2, a.3, ... a.10 GROUP BY 1,2,3,4,5,6,7,8,9,10 conveys the intent more clearly than listing out all the column names
- sk5t 6y ago
  It is also very helpful to avoid re-typing expressions. select a || 'baz', avg(b) from foo group by 1 order by 2;

grecy 6y ago

could '2' be a column name? ... meaning 'order by 2' would order it by the values in the column named '2', but which wasn't selected.

benesch 6y ago
In PostgreSQL, the grammar does not permit unquoted identifiers to start with a digit. So this is a syntax error: SELECT 'val' AS 2 Instead you must escape the identifier with double quotes: SELECT 'val' AS "2" So there is no ambiguity on that front in the ORDER BY clause. Either you have `ORDER BY 2`, which means order by the second column, or you have `ORDER BY "2"`, which means order by the column named "2".

adamzochowski 6y ago

Some SQL Servers, like MS-SQL have been slowly going into banning order by a number / constant expression, and flagging column numbers as error prone constructs. https://www.sql-server-performance.com/error408/ https://www.sql-server-performance.com/error408/