Hacker News Reader

Comment by Areading314 | Hacker News Reader

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

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

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

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/

Reply on news.ycombinator.com