getting the maximum

From: Michael Sterling <stermic_at_gw.co.jackson.mo.us>
Date: 22 Apr 2003 09:49:03 -0700
Message-ID: <f7359f44.0304220849.3191f989_at_posting.google.com>


i have a query where i'm attempting to select the maximum of the records returned. that is to say it will return multiple records with the same primary key, and of those i want the one with the highest percentage as indicated in the sample of returned data below. what my current query looks like is shown below the sample returned data. look at the all caps part of the select and where clauses

14-730-04-39-00-0-00-000   70   27760
15-620-02-03-00-0-00-000   50   229051
15-620-02-03-00-0-00-000   70   229051 <-- IS THE ONLY RECORD DESIRED

SELECT DISTINCT
    occ_history.parcel_number, MAX(OCC_HISTORY.PERC_COMPLETE),     occ_history.occupied_date,
    occup_values.occ_mkttl
FROM
    ascend30:informix.occ_history occ_history,     ascend30:informix.occup_values occup_values WHERE
    occ_history.parcel_number = occup_values.parcel_number AND

    occup_values.occ_type_code = 2 AND 
    OCC_HISTORY.PERC_COMPLETE >= 10 AND
    OCC_HISTORY.PERC_COMPLETE <= 70

GROUP BY
    occ_history.perc_complete, occ_history.parcel_number,
occ_history.occupied_date,
    occup_values.occ_mkttl
Received on Tue Apr 22 2003 - 18:49:03 CEST

Original text of this message