NEW! Finding/Sorting Top 4 Columns in a table
I need mySQL code to:
update table batting_life162
set pos1 = to the highest value contained in the following columns: g_c/(yrs-2), or g_1b/(yrs-2), or g_2b/(yrs-2), or g_3b/(yrs-2), or g_ss/(yrs-2), or g_lf/(yrs-2), or g_cf/(yrs-2), or g_rf/(yrs-2), or g_dh/(yrs-2)
concat that value with
"-2" if the column containing the highest value is "g_c"
"-3" if the column containing the highest value is "g_1b"
"-4" if the column containing the highest value is "g_2b"
"-5" if the column containing the highest value is "g_3b"
"-6" if the column containing the highest value is "g_ss"
"-7" if the column containing the highest value is "g_lf"
"-8" if the column containing the highest value is "g_cf"
"-9" if the column containing the highest value is "g_rf"
"-10" if the column containing the highest value is "g_dh"
set pos2 = to the 2nd highest value contained in g_c/(yrs-2), or g_1b/(yrs-2), or g_2b/(yrs-2), or g_3b/(yrs-2), or g_ss/(yrs-2), or g_lf/(yrs-2), or g_cf/(yrs-2), or g_rf/(yrs-2), or g_dh/(yrs-2)
and concatenate that value with
"-2" if the column containing the 2nd highest value is "g_c"
"-3" if the column containing the 2nd highest value is "g_1b"
"-4" if the column containing the 2nd highest value is "g_2b"
"-5" if the column containing the 2nd highest value is "g_3b"
"-6" if the column containing the 2nd highest value is "g_ss"
"-7" if the column containing the 2nd highest value is "g_lf"
"-8" if the column containing the 2nd highest value is "g_cf"
"-9" if the column containing the 2nd highest value is "g_rf"
"-10" if the column containing the 2nd highest value is "g_dh"
set pos3 = to the 3rd highest value contained in g_c/(yrs-2), or g_1b/(yrs-2), or g_2b/(yrs-2), or g_3b/(yrs-2), or g_ss/(yrs-2), or g_lf/(yrs-2), or g_cf/(yrs-2), or g_rf/(yrs-2), or g_dh/(yrs-2)
and concatenate that value with
"-2" if the column containing the 3rd highest value is "g_c"
"-3" if the column containing the 3rd highest value is "g_1b"
"-4" if the column containing the 3rd highest value is "g_2b"
"-5" if the column containing the 3rd highest value is "g_3b"
"-6" if the column containing the 3rd highest value is "g_ss"
"-7" if the column containing the 3rd highest value is "g_lf"
"-8" if the column containing the 3rd highest value is "g_cf"
"-9" if the column containing the 3rd highest value is "g_rf"
"-10" if the column containing the 3rd highest value is "g_dh"
set pos4 = to the 4th highest value contained in g_c/(yrs-2), or g_1b/(yrs-2), or g_2b/(yrs-2), or g_3b/(yrs-2), or g_ss/(yrs-2), or g_lf/(yrs-2), or g_cf/(yrs-2), or g_rf/(yrs-2), or g_dh/(yrs-2)
and concatenate that value with
"-2" if the column containing the 4th highest value is "g_c"
"-3" if the column containing the 4th highest value is "g_1b"
"-4" if the column containing the 4th highest value is "g_2b"
"-5" if the column containing the 4th highest value is "g_3b"
"-6" if the column containing the 4th highest value is "g_ss"
"-7" if the column containing the 4th highest value is "g_lf"
"-8" if the column containing the 4th highest value is "g_cf"
"-9" if the column containing the 4th highest value is "g_rf"
"-10" if the column containing the 4th highest value is "g_dh"
where yrs>2
include all functions, etc. I need to write into a script that i will execute all at once