471,075 Members | 929 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

multiple rows and columns MAX

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.

Jul 3 '06 #1
3 1269
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.
Jul 3 '06 #2
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.
Jul 3 '06 #3
>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.
Jul 3 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by aznFETISH | last post: by
12 posts views Thread by Philip Smith | last post: by
8 posts views Thread by Rigga | last post: by
7 posts views Thread by Billy Jacobs | last post: by
7 posts views Thread by samoore33 | last post: by
7 posts views Thread by =?Utf-8?B?TG9zdEluTUQ=?= | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.