Re: SQL question

In article <CG60zF.1H3_at_trc.amoco.com>, David Crowson <zdxc0d_at_amoco.com> wrote:
>But you don't need to do a subselect when you have a single and a group
>function in one select. You should use the 'group by' function.
>e.g.
>
>Select FIELD_A,max(FIELD_B)
>from XX
>order by FIELD_A
>group by FIELD_A

    The original question was how to find the FIELD_A value(s) of the row(s) containing the maximum FIELD_B value for the whole table. The above query will not be useful for that purpose, as one will have to inspect the entire (possibly very large) set of values to find the ones which have the maximum FIELD_B value; ordering by FIELD_A means that they will almost certainly be scattered randomly through the result set. The query should read:

    select [distinct] field_a, field_b /* DISTINCT if FIELD_A not unique */     from some_table
    [ where field_b is not null ] /* Omit if FIELD_B is mandatory */     order by field_b desc[, field_a]

This will of course list all rows with a non-null FIELD_B, not just the ones with the maximum value, but it will group all the ones of interest at the top of the list, and it may run faster than the subquery. (NULL sorts high, however, which is the reason for the WHERE clause if FIELD_B is not defined NOT NULL.) For ad-hoc purposes this is good. For a canned report, however, where you don't want the extraneous rows on output and can't hit ^C (or whatever your interrupt is) to abort the retrieval, the subquery is still the way to go.

-- 
Mike Stein			The above represents the Absolute Truth.
POB 10420			Therefore it cannot possibly be the official
Arlington, VA  22210		position of my employer.