Get Correct Highest Score

I have the following part of a query I'm working on in MYSQL.

SELECT
  MAX(CAST(MatchPlayerBatting.BatRuns AS SIGNED)) AS HighestScore
FROM
  MatchPlayerBatting

Select allOpen in new window

It returns the correct result. However there is another column I need it to work off.

That is if the maximum value it finds also has a value of "not out" within "BatHowOut", it should show the result as for example 96* rather than just 96.

How could this be done?

To help make the data correct, consider two cases:

BatRuns   BatHowOut
    96    not out
    96    lbw

BatRuns   BatHowOut
    96    not out
   102    lbw

Select allOpen in new window

This is the full query I'm trying to make it apart of:

SELECT Player.PageName, 
       Player.PlayerName, 
       Sum(MatchPlayerBatting.Batruns)            AS SumBatRuns, 
       Sum(MatchPlayerBatting.BatBalls)           AS SumBatBalls, 
       Sum(MatchPlayerBatting.BatMinutes)         AS SumBatMinutes, 
       Coalesce(Sum(BatFours), 0)                 AS SumBatFours, 
       Coalesce(Sum(BatSixes), 0)                 AS SumBatSixes, 
       Count(CASE 
               WHEN MatchPlayerBatting.BatHowOut <> '0' 
                    AND MatchPlayerBatting.BatHowOut <> 'absent hurt' THEN 
               MatchPlayerBatting.PlayerCommonName 
             end)                                 AS Innings, 
       Count(CASE 
               WHEN MatchPlayerBatting.BatHowOut = 'not out' 
                     OR MatchPlayerBatting.BatHowOut = 'retired hurt' THEN 
               MatchPlayerBatting.PlayerCommonName 
             end)                                 AS NotOuts, 
       Count(DISTINCT MatchPlayerBatting.MatchID) AS Matches, 
       Count(CASE 
               WHEN MatchPlayerBatting.BatRuns >= 50 
                    AND MatchPlayerBatting.BatRuns <= 99 THEN 
               MatchPlayerBatting.PlayerCommonName 
             end)                                 AS Fifties, 
       Count(CASE 
               WHEN MatchPlayerBatting.BatRuns = 0 
                    AND MatchPlayerBatting.BatBalls > 1 
                    AND ( MatchPlayerBatting.BatHowOut <> '0' 
                          AND MatchPlayerBatting.BatHowOut <> 'not out' 
                          AND MatchPlayerBatting.BatHowOut <> 'absent hurt' ) 
             THEN 
               MatchPlayerBatting.PlayerCommonName 
             end)                                 AS Ducks, 
       Count(CASE 
               WHEN MatchPlayerBatting.BatRuns = 0 
                    AND ( MatchPlayerBatting.BatBalls = 1 
                           OR MatchPlayerBatting.BatBalls = 0 ) 
                    AND ( MatchPlayerBatting.BatHowOut <> '0' 
                          AND MatchPlayerBatting.BatHowOut <> 'not out' 
                          AND MatchPlayerBatting.BatHowOut <> 'absent hurt' ) 
             THEN 
               MatchPlayerBatting.PlayerCommonName 
             end)                                 AS GoldenDucks, 
       Count(CASE 
               WHEN MatchPlayerBatting.BatRuns >= 100 THEN 
               MatchPlayerBatting.PlayerCommonName 
             end)                                 AS Hundreds, 
       Count(CASE 
               WHEN ( MatchPlayerBatting.BatHowOut = 'c' 
                       OR MatchPlayerBatting.BatHowOut = 'c & b' ) THEN 
               MatchPlayerBatting.BatFielderCommonName1 
             end)                                 AS Catches, 
       Count(CASE 
               WHEN ( BatHowOut = 's' ) THEN 
               MatchPlayerBatting.BatFielderCommonName1 
             end)                                 AS Stumpings 
FROM   MatchPlayerResult 
       INNER JOIN MatchPlayerBatting 
               ON MatchPlayerResult.MatchID = MatchPlayerBatting.MatchID 
                  AND MatchPlayerResult.MatchType = MatchPlayerBatting.MatchType 
                  AND MatchPlayerResult.PlayerID = 
                      MatchPlayerBatting.PlayerCommonName 
       INNER JOIN Player 
               ON MatchPlayerBatting.PlayerCommonName = Player.PlayerId 
                  AND MatchPlayerBatting.MatchID IN ( 413, 414, 415, 574, 
                                                      576, 577, 585, 586, 
                                                      587, 662, 663, 665, 
                                                      710, 712, 713, 782, 
                                                      783, 784, 844, 847, 
                                                      848, 998, 1000, 1003, 
                                                      1009, 1010, 1012, 1115, 
                                                      1116, 1151, 1152, 1153, 
                                                      1207, 1248, 1249, 1251, 
                                                      1316, 1339, 1342, 1533, 
                                                      1536, 1540, 1600, 1676, 
                                                      1677, 1934, 1938, 1940, 
                                                      1990, 1991 ) 
                  AND MatchPlayerBatting.MatchType = 'Test' 
                  AND ( TeamOne IN ( 'New Zealand' ) 
                         OR TeamTwo IN ( 'New Zealand' ) 
                         OR TeamThree IN ( 'New Zealand' ) ) 
GROUP  BY PageName, 
          PlayerName 

Select allOpen in new window