Selecting highest value for group of records?

[dchristie]

SELECT FIELD1, MAX(FIELD2) FROM TESTPF GROUP BY FIELD1

[Guest.Visitor]

DOH! I had the statement correct to begin with. Silly me didn't pay attention enough to the table and I picked one client that had only one entry, hence why the value never changed. Thanks anyway David. :-)

[David Abramowitz]

In addition to David's suggestion, the OPNQRYF command also has a <U>%max</u> function. Dave

[maldonadoj]

I have a simular problem but the records that I need are joined with another file. I have an item balance file with Item# and on hand# that I need to join to a price file with item#, effective date and price. The price file has multiple records per item# base on date and I need to return item#, on hand, and price for the max effective date. Can this be done in a single SQL statement?

[Guest.Visitor]

I've got a table with multiple records for a single customer. What I'm trying to do is select the ONE record, for that customer set, that has the highest value in a particular field. I thought MAX would do it for me, but it doesn't seem to. Any suggestion on what else works well? Normally in RPG I'd just SETGT then READPE, but I'm trying to convert to a true SQL statement. Thanks in advance!

[Guest.Visitor]

Select file1.item, file1.onhand, file2.price from file1, file2 where file1.item = file2.item and file2.date = (select max(date) from file2 a where a.item = file2.item)