为每个人从MySQL获取最新记录

I have a table like this

我有一张这样的桌子

╔════╦════════╦═════════════╦═════════════════╗
║ PK ║  NAME  ║   DEGREE    ║ YEAR_OF_PASSING ║
╠════╬════════╬═════════════╬═════════════════╣
║  1 ║ Shrey  ║ B.E.        ║            2004 ║
║  2 ║ Shrey  ║ High School ║            2000 ║
║  3 ║ Gaurav ║ B.E.        ║            2000 ║
║  4 ║ Gaurav ║ M.Sc.       ║            2002 ║
╚════╩════════╩═════════════╩═════════════════╝

How do I query to get a resultset of latest degree of each person as shown below?

如何查询每个人的最新程度的结果集，如下所示?

╔════╦════════╦════════╦═════════════════╗
║ PK ║  NAME  ║ DEGREE ║ YEAR_OF_PASSING ║
╠════╬════════╬════════╬═════════════════╣
║  1 ║ Shrey  ║ B.E.   ║            2004 ║
║  4 ║ Gaurav ║ M.Sc.  ║            2002 ║
╚════╩════════╩════════╩═════════════════╝

1 个解决方案

#1

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  Name, MAX(Year_Of_Passing) max_val
            FROM tableName
            GROUP BY Name
        ) b ON  a.name = b.name AND
                a.Year_Of_Passing = b.max_val

• SQLFiddle Demo
• SQLFiddle演示

UPDATE 1

更新1

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  Name, MAX(Year_Of_Passing) max_val, MAX(PK) max_pk
            FROM tableName
            GROUP BY Name
        ) b ON  a.name = b.name AND
                CASE WHEN b.max_val IS NULL
                     THEN a.pk = max_PK
                     ELSE a.Year_Of_Passing = b.max_val 
                  END

• SQLFiddle Demo
• SQLFiddle演示