multiple rows and columns MAX

[stassaf]

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.

[Roy Harvey]

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, st*****@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.

[stassaf]

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, st*****@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.

[Roy Harvey]

>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, st*****@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, st*****@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.