|
|
Posted by stassaf on 07/03/06 13:54
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.
Navigation:
[Reply to this message]
|