Re: SQL sub query help; getting several MAX() values

Try this :

select id, max(x), max(y), max(z) from test group by id;

hth

Yann

Kelly Gallagher a écrit dans le message ...
>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 Tue Oct 16 2001 - 01:56:01 CDT