can someone please tell me what is wrong with the following query
select 1
from table1 a,
table2 b
where a.pdate=max(b.pdate)
It is not compiled.
the other way to write this query is
set @pdate=pdate from table2
select 1
from table1 a,
table2 b
where a.pdate=max(b.pdate)
But I want to understand what is wrong with the first query.
Thanks
But I want to understand what is wrong with the first query.
The error message tells you something that could be of value to you.
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
The max() function is an aggregate that returns the max value for a set of rows. The where clause is used to filter rows. So if you use an aggregate in the place where you are doing the filtering it is not clear what rows you actually want the max value for.
A rewrite could look like this:
select 1
from dbo.table1 as a
where a.pdate = (
select max(b.pdate)
from dbo.table2 as b
);
even second query is wrong.
Correct way,
Select @pdate=max(pdate) from table2
select 1
from table1 a where a.pdate=@pdate
or,
select 1
from table1 a where a.pdate=(Select max(pdate) from table2)
if you mention another column name apart from aggregate column then you hv to use group by
