Hacker News Reader

Comment by anamexis | Hacker News Reader

Some of those queries are nonsense, but `select a, b from nums order by 2` is not one of them. `order by 2` here means "order by the second field in the select statement."

View on HN

One note: While using field number for anything is perfecrly valid SQL, this will not pass a code review if I'm the one doing the review. Reason being, it's not clear without looking at the field list what is being ordered. For extra shame do something like:

    select * from foo order by 2

Order of the fields when doing "select *" may be guaranteed (I don't know) but they have been dependent on when the feild was added to the database on every system I have worked with, and sometimes patches are applied in diffrent orders on diffrent databases.

If you have a Python script create your tables

    AND 1=1 
    AND this script has the table information stored in dicts in the {column name: column type} form
    AND this script is run by a python version where the dict order is undefined

you will get funny outputs from your queries that break production deployments in subtle and unique ways.

So yeah, just use column names (and name your computed columns!).

Yes, name your computed columns. Makes your life easier.

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

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