>>>>>Do you want to see the date and hour and minute when that max rating was achieved? This would make more sense but the query will be a bit more complex.
>>>>>
>>>>>I would go with the composed column solution for this case, e.g.
>>>>>
>>>>>select max(mRating) as MaxRating,
>>>>>RIGHT(max(cast(mRating as C(20)) + CAST(mIndex as C(14))),14) as WhenMaxRatingHappened,
>>>>>LEFT(CAST(mIndex as C(14)),6) AS YearMonth
>>>>>FROM Tab1
>>>>>GROUP BY 3
>>>>
>>>>Thank you for the above! The FULL JOIN produces this output:
>>>>
>>>>
>>>>SumMax's YYMM
>>>>20.5 201301
>>>>19.2 201302
>>>>
>>>>
>>>>For a report, it would really help to have the option of including the MAX ratings for each table, such as:
>>>>
>>>>
>>>>MaxT1 Max T2 SumMax's YYMM
>>>>5.6 4.1 9.7 201301
>>>>2.4 1.3 3.7 201302
>>>>
>>>>
>>>>And here it is:
>>>>
>>>>
>>>>select ;
>>>>t1.RatingMax as t1MAX,;
>>>>t2.RatingMax as t2MAX,;
>>>>NVL(T1.RatingMax,0) + NVL(T2.RatingMAX,0) as SumMAXs, ;
>>>>NVL(T1.yyMMddhh, T2.yyMMddhh) AS yyMMddhh ;
>>>>FROM;
>>>>T1 ;
>>>>FULL JOIN ;
>>>>T2 ;
>>>>ON T1.yymmddhh = T2.yymmddhh
>>>>
>>>
>>>Yes, this is correct.
>>
>>
>>select max(mRating) as MaxRating;
>>RIGHT(max(cast(mRating as C(20)) ;
>>+;
>>CAST(mIndex as C(14))),14) ;
>>as ;
>>WhenMaxRatingHappened;
>>LEFT(CAST(mIndex as C(14)),6) AS YearMonth;
>>FROM Tab1;
>>GROUP BY 3
>>
>
>You're now missing a comma after MaxRating.
>
>BTW, why do you need this column anyway? I thought you didn't need it for your report.
to combine when the date and time of maxRating in my report
select max(mRating) as MaxRating ,;
RIGHT(max(cast(mRating as C(20)) + ;
CAST(mIndex as C(14))),14) ;
as WhenMaxRatingHappened ,;
CAST(mIndex as C(14))),14) AS YearMonth ;
FROM Tab1;
GROUP BY 3
