-2

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.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
theweeknd
  • 277
  • 1
  • 12
  • 7
    what error you are getting...one reason might be you are giving column name same as existing column – Pranay Rana Jun 26 '15 at 07:00
  • 3
    The first query in your question should run without errors as is. What is the error message? – Vladimir Baranov Jun 26 '15 at 07:19
  • 1
    Its running without any error, http://sqlfiddle.com/#!3/b325c/1 – Arulkumar Jun 26 '15 at 07:23
  • This is the error:Could not execute statement. Invalid use of an aggregate function SQLCODE=-150, ODBC 3 State="42000" Line 1, column 1 select max(run_id) as run_id from my_table where run_id > 50 – theweeknd Jun 26 '15 at 08:33
  • 1
    I do not understand the hate, this is a legit question and i dont know why doesnt it give an error in fiddle. Im using Sybase IQ, but that syntax is ansi sql so it shouldnt matter what dbms you use. This is not some homework assignment...if you cant explain just ignore the post, dont hate it. – theweeknd Jun 26 '15 at 08:36
  • 1
    @theweeknd, I will respond with an answer as soon as I get home or I'm available. This is definitely a legit question IMHO. Sometimes the community is harsh, I recommend not to stress over it. – anar khalilov Jun 26 '15 at 13:41
  • @ Anar Khalilov thanks for the support :) – theweeknd Jun 29 '15 at 12:57

1 Answers1

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;
Razvan Socol
  • 5,426
  • 2
  • 20
  • 32
  • Thank you, this is exactly what i was wondering, very good explanation. If i could i would give you multiple upvotes for the answer. And to the other people who downloaded this, learn how to be a community and dont be so arrogant, just because you dont know how to explain something it doesnt mean the question is stupid, it just shows how ignorant you are. – theweeknd Jul 02 '15 at 08:10