Primary Position

MySQL

Using the appearances table:

SELECT playerID
, yearID
, teamID
, (CASE WHEN G_p = GREATEST(G_p,G_c,G_1b,G_2b,G_3b,G_ss,G_lf,G_cf,G_rf,G_dh)
THEN "P"
WHEN G_c = GREATEST(G_p,G_c,G_1b,G_2b,G_3b,G_ss,G_lf,G_cf,G_rf,G_dh)
THEN "C"
WHEN G_1b = GREATEST(G_p,G_c,G_1b,G_2b,G_3b,G_ss,G_lf,G_cf,G_rf,G_dh)
THEN "1B"
WHEN G_2b = GREATEST(G_p,G_c,G_1b,G_2b,G_3b,G_ss,G_lf,G_cf,G_rf,G_dh)
THEN "2B"
WHEN G_3b = GREATEST(G_p,G_c,G_1b,G_2b,G_3b,G_ss,G_lf,G_cf,G_rf,G_dh)
THEN "3B"
WHEN G_ss = GREATEST(G_p,G_c,G_1b,G_2b,G_3b,G_ss,G_lf,G_cf,G_rf,G_dh)
THEN "SS"
WHEN G_lf = GREATEST(G_p,G_c,G_1b,G_2b,G_3b,G_ss,G_lf,G_cf,G_rf,G_dh)
THEN "LF"
WHEN G_cf = GREATEST(G_p,G_c,G_1b,G_2b,G_3b,G_ss,G_lf,G_cf,G_rf,G_dh)
THEN "CF"
WHEN G_rf = GREATEST(G_p,G_c,G_1b,G_2b,G_3b,G_ss,G_lf,G_cf,G_rf,G_dh)
THEN "RF"
WHEN G_dh = GREATEST(G_p,G_c,G_1b,G_2b,G_3b,G_ss,G_lf,G_cf,G_rf,G_dh)
THEN "DH"
ELSE "UNK"
END ) AS POS
FROM appearances
ORDER BY POS;

From the fielding table:

SELECT playerID
, yearID
, teamID
, MAX(G) AS G
, POS
FROM (SELECT * from fielding
WHERE IF(yearID>1995 AND POS = "OF",1,0) != 1 ORDER BY G Desc) f
GROUP BY playerID, yearID, teamID;