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.
Comments
Post a Comment