Re: Tricky question for simple SQL

From: Kristian Myllymäki <kristian_at_kmja.com>
Date: Thu, 13 May 2004 18:00:44 +0200
Message-ID: <3XMoc.12679$EV2.108122@amstwist00>

How about,

select year, month, adjust_demand_id, object_value, min(prt_id) prt_id from ( select

          year,
          month,
          adjust_demand_id,
          min(object_value) over (partition by year, month, adjust_demand_id
order by object_value) min_object_value,
          object_value,
          prt_id
        from [your table]

  )
where min_object_value = object_value
group by year, month, adjust_demand_id, object_value

/Kristian

"Christian Svensson" <chse30_at_hotmail.com> wrote in message news:ccc2a7eb.0405130701.4ac319aa_at_posting.google.com...
> Hi Oracle SQL gurus out there,
>
> I have a tricky question, in a table I have these data:
>
> YEAR MONTH ADJUST_DEMAND_ID OBJECT_VALUE PRT_ID
> 2004 10 -93450 -620 -906440
> 2004 10 -93450 -620 -906703
> 2004 10 -93450 -130 -906468
> 2004 10 -93450 -130 -906731
> 2004 10 -93450 -128 -906621
> 2004 10 -93450 -126 -907093
> 2004 10 -93450 -120 -906683
> 2004 10 -93450 -120 -907226
> 2004 10 -93450 -116 -906501
> 2004 10 -93450 -116 -906838
> 2004 10 -93450 -114 -906544
> 2004 10 -93450 -114 -906966
> 2004 10 -93154 -436 -906439
> 2004 10 -93154 -436 -906702
> 2004 10 -93154 -274 -978819
> 2004 10 -93154 -274 -906419
> 2004 10 -93154 -271 -906110
> 2004 10 -93154 -270 -907239
> 2004 10 -93154 -269 -978739
> 2004 10 -93154 -269 -978741
> 2004 10 -93154 -269 -978744
> 2004 10 -93154 -269 -906373
> 2004 10 -93154 -242 -906090
> 2004 10 -93154 -236 -906435
> 2004 10 -93154 -236 -906701
> 2004 10 -93154 -230 -907057
> 2004 10 -93154 -177 -907250
> 2004 10 -93154 -86 -906543
> 2004 10 -93154 -86 -906965
> 2004 10 -93154 -84 -906717
> 2004 10 -93154 -81 -906467
> 2004 10 -93154 -81 -906730
> 2004 10 -92562 -1019 -906439
> 2004 10 -92562 -1019 -906702
> 2004 10 -92562 -757 -907057
> 2004 10 -92562 -502 -906419
> 2004 10 -92562 -502 -978819
> 2004 10 -92562 -488 -907239
> 2004 10 -92562 -480 -906110
> 2004 10 -92562 -470 -906373
> 2004 10 -92562 -470 -978744
> 2004 10 -92562 -470 -978739
> 2004 10 -92562 -470 -978741
> 2004 10 -92562 -387 -906090
> 2004 10 -92562 -345 -906435
> 2004 10 -92562 -345 -906701
> 2004 10 -92562 -334 -907250
> 2004 10 -92562 -209 -906543
> 2004 10 -92562 -209 -906965
> 2004 10 -92562 -198 -906717
> 2004 10 -92562 -177 -906467
> 2004 10 -92562 -177 -906730
>
> and I want this output, i.e min(object_value) for each combination of
> year, month, adjust_demand_id, and its prt_id (if same object_value
> then it does not matter which prt_id it selects)
>
> YEAR MONTH ADJUST_DEMAND_ID OBJECT_VALUE PRT_ID
> 2004 10 -93450 -620 -906544
> 2004 10 -93154 -436 -906730
> 2004 10 -92562 -1019 -906730
>
> I have tried with several Group By and min() but can not get it right.
>
> There gotta be some Oracle function that can cope with this.
>
> Thanks anyone for any comment on this.
>
> Cheers !
>
> /Christian
Received on Thu May 13 2004 - 11:00:44 CDT