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

Open 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

Open 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 

Open in new window

PeterErhardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
in MySQL, this is indeed not "THAT" simple.
anyhow, see this article, it shows your options also for MySQL:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
PeterErhardAuthor Commented:
OK, thanks anyway.  Will see if someone else comes along :)
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think I misunderstood the request...
maybe something like this
SELECT
  replace(MAX( CAST(MatchPlayerBatting.BatRuns AS DECIMAL(18,1))
         + CASE WHEN BatHowOut = 'not out' then 0.5 else 0 end 
         ), '.5', '*')
AS HighestScore
FROM
  MatchPlayerBatting 

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PeterErhardAuthor Commented:
Thanks heaps angel, that seems to do the trick!

One minor thing though, its display the results as 46.0 for example rather than 46. How can I get rid of the decimal but keep this working? Or should I just remove the decimal through code?
hs.JPG
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
SELECT
  replace(replace(MAX( CAST(MatchPlayerBatting.BatRuns AS DECIMAL(18,1))
         + CASE WHEN BatHowOut = 'not out' then 0.5 else 0 end 
         ), '.5', '*'), '.0', '')
AS HighestScore
FROM
  MatchPlayerBatting 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterErhardAuthor Commented:
Thank you so very much for that, works perfectly :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.