Posted by Aaron Lawrence on 02/15/06 07:35

Gert-Jan Strik wrote:
> Well, your original query seems pretty simply to me.

Yes, it is. The problem arises when there is other criteria, which have
to be duplicated on both where clauses.

> 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(..))

I wouldn't use that proprietary extension either.

Thanks for your help.

>
> 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]