Comment by benesch | Hacker News Reader

Comment by benesch | Hacker News Reader

Parent Full thread

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

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/

Interesting -- didn't know it did this

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

It is also very helpful to avoid re-typing expressions.

select a || 'baz', avg(b) from foo group by 1 order by 2;

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.

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

Reply on news.ycombinator.com