>I have a table containing a key, date and 48 values for each record.
>I need to retrieve the maximum value for each key for each month - but this value may be any of the 48 values - e.g.
>Key Date f1 f2 f3 f4
>A00408, 01/02/1999, 12, 15, 16, 18
>A00408, 01/02/1999, 13, 18, 20, 22
>I need to return the maximum value and field in the example above it would be f4 and 22. You can do this in Excel using the MAX() function and providing a range. Would my best best be to paste this data into Excel and use Max() or can it be done in 1 or a couple of SELECT statements.
>
>Thanks,

Martin,

Your best would be to normalize the table design and get rid of the multivalued fields. Then your request would be a walk in the park.

Instead of your table design make the table this way;

Name: DataTable
 Key     Date         Value
A00408 01/02/1999       12
A00408 01/02/1999       15
A00408 01/02/1999       16
A00408 01/02/1999       18
A00408 01/02/1999       13
A00408 01/02/1999       18
A00408 01/02/1999       20
A00408 01/02/1999       22

Now you get your answer by;

SELECT PrimaryKey, Key, CMONTH(Date) , MAX(Value) ;
  FROM DataTable ;
 GROUP BY 2,3 ;
  INTO CURSOR MyResults

The MyResults cursor wil have one record for each month and Key that has the highest value for that month and key.