>>>>>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.
You are right. Much more importantly, how do I expand the FULL JOIN to include more than 2 tables?
Or, should I just do 2 tables at a time. First join T1 and T2.
Then take the result and join it with T3, etc? Thank you, Steve
select ;
t1.RatingMax as t1MAX,;
t2.RatingMax as t2MAX,;
t3.RatingMax as t3MAX,;
NVL(T1.RatingMax,0) + NVL(T2.RatingMAX,0) + NVL(T3.RatingMAX,0)as SumMAXs, ;
NVL(T1.yyMMddhh, T2.yyMMddhh, T3.yyMMddhh) AS yyMMddhh ;
FROM;
T1 ;
FULL JOIN ;
T2 ;
ON T1.yymmddhh = T2.yymmddhh = T3.yymmddhh
