Re: Tricky question for simple SQL

chse30_at_hotmail.com (Christian Svensson) 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.
>

If I understand well your requirement:

select year, month, adjust_demand_id, min(object_value || ' ' || PRT_ID) from TABLE
group by year, month, adjust_demand_id

(not tested).

HTH Daniel Received on Thu May 13 2004 - 14:52:41 CDT