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>
GROUP BY
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_mkttlReceived on Tue Apr 22 2003 - 18:49:03 CEST