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