🚀 go-pugleaf

"Arno Brinkman" <bnewsgroups @ abvisie.nl> wrote in message
news:3de0d0f0$1@newsgroups.borland.com...
> Hi,
>
> > How does one select a row in a table in which a column value is the
> maximum
> > value of all the rows ? The MAX(column_name) syntax does not appear that
> it
> > can be used in a WHERE clause but only in a SELECT clause to get the
> maximum
> > value itself. Instead I want all column values for a row where a
> particular
> > column has the maximum value. It seem as if the only way to do this is
to
> > first SELECT MAX(column_name) from ATABLE, and then use the value
returned
> > to to a SELECT * FROM ATABLE WHERE column_name = mymax_value .
>
> Indeed you can't use MAX,MIN,AVG etc. in the WHERE clause, but you can use
> them in the HAVING clause. Using having in your case doesn't seem the
> resolution. You can use a sub-query in the WHERE clause or write a stored
> procedure with the solution you suggest.
>
> SELECT
>   *
> FROM
>   ATable
> WHERE
>   ATable.AField = (SELECT MAX(AField) FROM ATable)

Thanks, I managed to realize that this use of sub-query was thew answer.

>
> I'm afraid that the above query will be slow, so it's faster to use an SP.

OK, thanks for the hint to use a stored procedure instead.