5 ms·

No, it is using the constant expression 2 as a sort key, resulting in a random order which should be expected Edit -- this is wrong see below

benesch 6y ago
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/

benesch 6y ago

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/