How to find MIN with a duplicate value and replace MIN value with varchar

Question

I'm trying to find the minimum value for a certain ID during a certain season and replacing that value with an abbreviation.

I have these two tables:

table: batplayerlevelyear                    table: levels
-------------------------                    --------------------------
pid   season   levelid                       levelid abbrev
-------------------------                    --------------------------
121   2008     3                             1       MLB
121   2008     4                             2       AAA
121   2008     5                             3       AA
121   2009     1                             4       A+
121   2009     2                             5       A
121   2010     1                             6       A-
122   2009     4                             7       R
122   2009     3
122   2010     3
122   2011     2

What I want to do is:

table: newtable
--------------------
pid   season  abbrev
--------------------
121   2008    AA
121   2009    MLB
121   2010    MLB
122   2009    AA
122   2010    AA
122   2011    AAA

This is the sort of code that I've tried so far leaving me with almost what I want, but replacing the abbrev instead of the levelid

select
batplayerlevelyear.pid,
batplayerlevelyear.season,
min(levels.levelid) as highest_level

from batplayerlevelyear

leftjoin levels on batplayerlevelyear.levelid=levels.levelid

group by pid, season

order by pid

Answer 1

Try this:

SELECT a.pid, a.season, l.abbrev
FROM   (SELECT batplayerlevelyear.pid,
               batplayerlevelyear.season,
               Min(levels.levelid) AS highest_level
        FROM   batplayerlevelyear
        GROUP  BY pid, season) a
       LEFT JOIN levels l ON a.highest_level = l.levelid