SQL sub query help; getting several MAX() values

Hi all,

I am working with a table MyTable that has multiple records per ID value. I need to get a result set from MyTable that has only one row per ID value which contains several 'max' values of various fields. This is not very clear so let me give an example of what I need.

MyTable

ID     X     Y    Z
1      5     10   1
1      6     5    3
1      9          2
2            15   9
2      2     3    10
2      5     20   1

I need a query that will return the following result set for ID 1 (max values each for X, Y and Z):

ID     X     Y     Z
1      9     10    3

and the following for ID 2:

ID     X     Y     Z
2      5     20    10

I can get the max value for X with the following query : select id, x from MyTable where id=1 and x=(select max(x) from MyTable where id=1)
,
but am having trouble figuring out how to write SQL code that will give me more than one maximum field value for a given ID value. Any and all help is appreciated !

Kelly Gallagher
Applications Analyst Programmer
UNC - Chapel Hill Received on Mon Oct 15 2001 - 16:15:23 CDT