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

by

[ad_1]

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

[ad_2]

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