Hi All,
I'm using the sample table:

ID | CODE | V1 | V2 | V3
----------------------------------------------------------------
1 3 10 3 43
1 4 9 8 22
1 2 6 2 55
1 5 57 12 6

I want to get for a given ID the MAX(V1), MAX(V2), MAX(V3)
and their respective CODEs.

For the above table the returned record for ID=1 should be:
v1=57, v2=12, v3=55, code_v1=5, code_v2=5, code_v3=2

currently I've got a very messy solution, I'm
looking for an elegant way to do this.

10x,
Assaf.

You do not say how you want to hand duplicates, where the same value
is the max for more than one code.


SELECT X.*,
(select min(CODE) from Whatever as W1
where W1.ID = X.ID
and W1.V1 = X.V1) as code_v1,
(select min(CODE) from Whatever as W2
where W2.ID = X.ID
and W2.V2 = X.V1) as code_v2,
(select min(CODE) from Whatever as W3
where W3.ID = X.ID
and W3.V3 = X.V3) as code_v3
FROM (select ID, max(V1) as V1, max(V2) as V2, max(V3) as V3
from Whatever
group by ID) as X

Roy Harvey
Beacon Falls, CT

On 3 Jul 2006 06:20:33 -0700, stassaf@gmail.com wrote:

>Hi All,
>I'm using the sample table:
>
>ID | CODE | V1 | V2 | V3
>----------------------------------------------------------------
>1 3 10 3 43
>1 4 9 8 22
>1 2 6 2 55
>1 5 57 12 6
>
>I want to get for a given ID the MAX(V1), MAX(V2), MAX(V3)
>and their respective CODEs.
>
>For the above table the returned record for ID=1 should be:
>v1=57, v2=12, v3=55, code_v1=5, code_v2=5, code_v3=2
>
>currently I've got a very messy solution, I'm
>looking for an elegant way to do this.
>
>10x,
>Assaf.

Hi Roy,

10x for your prompt reply.
lets say duplicates are allowed, how can i simplify the solutions?

Regards,
Assaf.

Roy Harvey wrote:
> You do not say how you want to hand duplicates, where the same value
> is the max for more than one code.
>
>
> SELECT X.*,
> (select min(CODE) from Whatever as W1
> where W1.ID = X.ID
> and W1.V1 = X.V1) as code_v1,
> (select min(CODE) from Whatever as W2
> where W2.ID = X.ID
> and W2.V2 = X.V1) as code_v2,
> (select min(CODE) from Whatever as W3
> where W3.ID = X.ID
> and W3.V3 = X.V3) as code_v3
> FROM (select ID, max(V1) as V1, max(V2) as V2, max(V3) as V3
> from Whatever
> group by ID) as X
>
> Roy Harvey
> Beacon Falls, CT
>
> On 3 Jul 2006 06:20:33 -0700, stassaf@gmail.com wrote:
>
> >Hi All,
> >I'm using the sample table:
> >
> >ID | CODE | V1 | V2 | V3
> >----------------------------------------------------------------
> >1 3 10 3 43
> >1 4 9 8 22
> >1 2 6 2 55
> >1 5 57 12 6
> >
> >I want to get for a given ID the MAX(V1), MAX(V2), MAX(V3)
> >and their respective CODEs.
> >
> >For the above table the returned record for ID=1 should be:
> >v1=57, v2=12, v3=55, code_v1=5, code_v2=5, code_v3=2
> >
> >currently I've got a very messy solution, I'm
> >looking for an elegant way to do this.
> >
> >10x,
> >Assaf.

>lets say duplicates are allowed, how can i simplify the solutions?

The example I posted handles duplicates by returning the smallest,
min(CODE). Returning the largest would be easy enough, max(CODE).

Anything else woule become more complicated. Otherwise I think that
pretty much IS the simple solution! 8-)

Roy


On 3 Jul 2006 06:54:11 -0700, stassaf@gmail.com wrote:

>Hi Roy,
>
>10x for your prompt reply.
>lets say duplicates are allowed, how can i simplify the solutions?
>
>Regards,
>Assaf.
>
>Roy Harvey wrote:
>> You do not say how you want to hand duplicates, where the same value
>> is the max for more than one code.
>>
>>
>> SELECT X.*,
>> (select min(CODE) from Whatever as W1
>> where W1.ID = X.ID
>> and W1.V1 = X.V1) as code_v1,
>> (select min(CODE) from Whatever as W2
>> where W2.ID = X.ID
>> and W2.V2 = X.V1) as code_v2,
>> (select min(CODE) from Whatever as W3
>> where W3.ID = X.ID
>> and W3.V3 = X.V3) as code_v3
>> FROM (select ID, max(V1) as V1, max(V2) as V2, max(V3) as V3
>> from Whatever
>> group by ID) as X
>>
>> Roy Harvey
>> Beacon Falls, CT
>>
>> On 3 Jul 2006 06:20:33 -0700, stassaf@gmail.com wrote:
>>
>> >Hi All,
>> >I'm using the sample table:
>> >
>> >ID | CODE | V1 | V2 | V3
>> >----------------------------------------------------------------
>> >1 3 10 3 43
>> >1 4 9 8 22
>> >1 2 6 2 55
>> >1 5 57 12 6
>> >
>> >I want to get for a given ID the MAX(V1), MAX(V2), MAX(V3)
>> >and their respective CODEs.
>> >
>> >For the above table the returned record for ID=1 should be:
>> >v1=57, v2=12, v3=55, code_v1=5, code_v2=5, code_v3=2
>> >
>> >currently I've got a very messy solution, I'm
>> >looking for an elegant way to do this.
>> >
>> >10x,
>> >Assaf.