Not sure if it should be ported to Clickhouse.

It's available is MySQL and some other RDBMS, but it's is very controversial, and it's definitely a bad practice. It was removed from SQL standard in 1999. The only plus of that it that it allows you to gain few second on typing SQL, but all other aspects are minuses.

Please see:
https://dba.stackexchange.com/a/86612
https://sqlblog.org/2009/10/06/bad-habits-to-kick-order-by-ordinal

Other databases get rid of that, see, for example:

Another question people often ask is whether SQL Server will stop supporting specifying ordinal positions of expressions from the SELECT list in the ORDER BY clause in SQL Server, as in the following example:
FROM dbo.T1
ORDER BY 3, 1;
Regardless of whether this syntax is standard and SQL Server will stop supporting it in the future, it’s a bad practice to use it in production code because you could make revisions to the SELECT list and forget to make the corresponding revisions to the ORDER BY list. Also, the code is less readable this way. The best practice is to specify the aliases of the expressions that appear in the SELECT list in the ORDER BY clause.

As for conformance to the ANSI SQL standard, ANSI SQL-92 was the last standard that still supported this syntax. ANSI SQL:1999 removed support for this syntax. SQL Server 2008 still supports this syntax, and currently its documentation doesn’t refer to it as a deprecated feature, but it’s on Microsoft’s list of SQL Server features that are on their way out, so it’s recommended to refrain from using it.

Even MySQL documentation says that "Use of column positions is deprecated because the syntax has been removed from the SQL standard."

PS. And don't be so lazy while typing your query :) It will give you a lot of long term pluses. It's the same as proper code formatting, and good variable naming.

PPS. In Clickhouse you can use column aliases inside WHERE / GROUP BY / HAVING as a column shortcuts. So if you really like to create bad-readable queries which reference column by their positions you can always do similar:

SELECT ... as c1, ... as c2, ... as c3 FROM ... GROUP BY c1,c2,c2;

Feature request: GROUP BY column number support #2592

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions