Re: using the aggregate function max()

John Fabiani, 23.09.2011 04:49:
> I need to find the max(date) of a field but I need that value later in my
> query.
>
> If I
> select max(x.date_field) as special_date from (select date_field) from table
> where ...)x
>
> I get one row and column.
>
> But now I want to use that field in the rest of the query
>
> select y.*,  max(x.date_field) as special_date from (select date_field) from
> table where ...)x
> from aTable y where y.somefield = special_date.
>
> The above only returns one row and one column the "special_date."
>

Not sure I undersand you correctly, but shouldn't the following be doing what you want:

SELECT y.*
FROM table y
WHERE y.somefield = (SELECT max(x.date_field)                     FROM table x                     WHERE ...)

Thomas