Why does MAX(Column) as Column give error, and MAX(Column) as max_Column does not?

Question

I was just wondering, why does:

select max(run_id) as run_id from my_table where run_id > 50;

It gives an error and

select max(run_id) as max_run_id from my_table where run_id > 50;

select max(run_id) from my_table where run_id > 50;

the above two queries does not give an error.

Let's say the structure of the table is,

create table my_table(
run_id int,
something varchar(10))

This table has 100 run_id's.

I know you can't use where clause with aggregate functions.

Is it because we rename the column (as max_run_id) and the sql is treating it as a separate column, where if the name was the same as the original column it sees the aggregate function and gives the error because of it? Or can someone explain that with better terms.

Answer 1

Indeed, this should work (and it works in other DBMS-s, like SQL Server, Oracle, MySQL, etc). You could say it's a bug in Sybase IQ or (more accurately) a non-standard implementation.

It seems that Sybase IQ allows using aliases anywhere in the query, because the documentation says: "alias-names can be used throughout the query to represent the aliased expression. [...] If you use the same name or expression for a column alias as the column name, the name is processed as an aliased column, not a table column name.

The error message indicates that "a SELECT statement cannot contain an aggregate function within a predicate in the WHERE clause"

In other words, Sybase IQ understands your query as:

select max(run_id) as run_id from my_table where max(run_id) > 50;