getting the maximum from multiple returns
From: Michael Sterling <stermic_at_gw.co.jackson.mo.us>
Date: 24 Apr 2003 06:51:11 -0700
Message-ID: <f7359f44.0304240551.20a3a079_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
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
GROUP BY
Date: 24 Apr 2003 06:51:11 -0700
Message-ID: <f7359f44.0304240551.20a3a079_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 OF THE LAST 2 BECAUSE IT HAS THE HIGHER NUMBER OF 70.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 Thu Apr 24 2003 - 15:51:11 CEST