CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Shrey
Shrey

Reputation: 2414

Fetching Latest Record from MySQL for each individual

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 ║
╚════╩════════╩════════╩═════════════════╝

Upvotes: 3

Views: 143

Answers (1)

John Woo
John Woo

Reputation: 263883

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

UPDATE 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

Upvotes: 5

Related Questions