Re: getting the maximum from multiple returns

>> I have a query where I'm attempting to select the maximum of the
records [sic] returned. That is to say it will return multiple records [sic] with the same primary key [sic], and of those I want the one with the highest percentage as indicated in the sample of returned data below. <<

Rows are not records; primary keys are BY DEFINITION unique. Only scalar values have a max. This posting makes no sense.; Since you also did not bother to post any DDL, we can only guess as to what things look like. You show three columns in your sample data (no column names!!) and four columns in your query.

Here is a wild guess, based on the specs you did not post.

SELECT H1.parcel_number, H1.perc_complete,

       H1.occupied_date, V1.occ_mkttl
  FROM ascend30:informix.occ_history AS H1,

       ascend30:informix.occup_values AS V1  WHERE H1.perc_complete

• (SELECT MAX(H2.perc_complete) FROM ascend30:informix.occ_history AS H2 WHERE H2.parcel_number = H1.parcel_number AND H2.perc_complete BETWEEN 10 AND 70) AND V1.occ_type_code = 2;

If your boss makes you work from specs like this, I hope you get paid by th hour. Received on Fri Apr 25 2003 - 04:31:22 CEST