Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Tricky question for simple SQL
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]
/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
![]() |
![]() |