mysql - MAX with extra criteria -

i have following part of query i'm working on in mysql.

select   max(cast(matchplayerbatting.batruns signed)) highestscore   matchplayerbatting 

it returns correct result. there column need work off.

that if maximum value finds has value of "not out" within "bathowout", should show result example 96* rather 96.

how done?

to make data concrete, consider 2 cases:

batruns   bathowout     96    not out     96    lbw  batruns   bathowout     96    not out    102    lbw 

for first data, answer should '96*'; second, '102'.

how ordering scores in descending order , selecting first record?

select concat(batruns , case when bathowout = 'not out' '*' else '' end)   mytable order cast(batruns signed) desc,         (case when bathowout = 'not out' 1 else 2 end) limit 1; 

sample here.

if want find highest score score each player, here solution may not elegant, quite effective.

select playerid,        case when runs != round(runs)                  concat(round(runs),'*')             else                  round(runs)        end highest_score   (select playerid,                max(cast(batruns decimal) +                     case when bathowout = 'not out' 0.1 else 0 end                   ) runs           matchplayerbatting          group playerid) max_runs; 

this takes advantage of fact that, runs can never fractions, whole numbers. when there tie highest score , 1 of them unbeaten, adding 0.1 unbeaten score make highest. can later removed , concatenated *.

sample here.