Posted by Roy Harvey on 07/03/06 13:44

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.

[Back to original message]