>select ; >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 >>
>TheDate MaxT1 MaxT2 SumMAXs >201301 2.5 1.3 3.8 >>
>select ; >mRating ,; && tried to add in the mRating field, so it would be in the result >MAX(mRating) as RatingMAX ,; >substr(CAST(mIndex as C(14)),1,6) as yyMMddhh ; >from tab1 ; >GROUP BY 3 && tried to change this to 3 since want to group on 3rd column which is yyMMdd? >>
>select ; >MAX(mRating) as RatingMAX ,; >substr(CAST(mIndex as C(14)),1,6) as yyMMddhh ; >from tab1 ; >GROUP BY 2 >Your last question doesn't have much sense since you're selecting maximum rating. Which rating do you want to see in addition to that?
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