This is exactly what I needed ! I had tried the following code earlier as well:
select id, max(x), max(y), max(z) from test where id='1'
and of course got an error. Never thought about grouping by id . Many, many thanks.
Kelly Gallagher
"Yann CAUCHARD" <yann.cauchard_at_spcconsultants.com> wrote in message news:<9qgljm$234j$1_at_news4.isdnet.net>...
> 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 - 07:58:08 CDT
