[Solved] Return each name and other field corresponding to maximal value of third field in SQL

by


In most cases, you’ll find that the procedure for this is relatively universal. The following example will work in MySQL, MSSQL and DB2 (among others).

SELECT
    a.name,
    a.travel_date,
    a.total
FROM test_table AS a

INNER JOIN ( SELECT  `name`, MAX(total) AS `total` FROM test_table GROUP BY `name` ) AS b
ON  a.name = b.name
AND a.total = b.total;

Here’s an example of the sample data I worked with including the results after running the query.
Data example
–
Results

Edit: As jarlh, the initial query I wrote was indeed wrong. The following query should provide the results that you requested in the comment below.

3

solved Return each name and other field corresponding to maximal value of third field in SQL