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.

[quoted text, click to view]

SELECT column_a, MAX(column_b)
FROM table_name
GROUP BY column_a

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net

[quoted text, click to view]

Huh? Won't that show me the max of column_b for every value of column_a.
I only want to see the ONE value of column_a where column_b is at max.

[quoted text, click to view]

Is it this simple?

Select top 1 column_a from table order by column_b desc

hth

[quoted text, click to view]

Is it this simple?

Select top 1 column_a from table order by column_b desc

hth

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


[quoted text, click to view]