Posted by Gert-Jan Strik on 02/14/06 23:07

Well, your original query seems pretty simply to me.

There is no other (simpler) way to write this if you want to write
standard (ANSI SQL) code. If you don't mind using Microsoft proprietary
syntax, then you can use SELECT TOP 1 .. ORDER BY column_b DESC, as
suggested by someone else. If column_b is not unique, you can use SELECT
TOP 1 WITH TIES.

Personally, if there is no real reason to use MS syntax then I prefer
standard SQL. Unless the query is called x times per second, I would
stay with SELECT .. WHERE column_b = (SELECT MAX(..))

Gert-Jan


Aaron Lawrence wrote:
>
> Hello all.
>
> Is there a simpler way to write something like this:
>
> select column_a from table
> where column_b = ( select max( column_b ) from table )
>
> ie. find the row where column_b is at it's maximum and return another
> column's value?
>
> something like (hypothetically)
>
> select column_a from table
> where column_b is max
>
> Leaving aside the issue of whether max( column_b ) is unique ...
>
> Thanks.
>
> Aaron

[Back to original message]