>>>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
