How to Select Only Rows with the Maximum Value in a Specific Column in SQL?

SQL only selects the maximum line in the column

Overview

Given a watch containing repeated lines, the task is to choose a line with the maximum value in a specific column.

Solution

Method 1: Grouping and aggregation

The most direct solution is to use Celebration and Polymerization function:

GROUP BY This will return the standard identity (ID) and maximum Rev value of each group. MAX()

Method two: self -connection and filtering

<code class="language-sql">SELECT id, MAX(rev)
FROM YourTable
GROUP BY id</code>

A more advanced method is to use the ID column as the connection condition, and perform the left connection with the table itself:

This method first connects each line to all other rows with the same ID. Then, it uses the

computing character in the connection condition to delete all REV values ​​higher than the current row. Finally, the sub -query ensures that only a higher REV value is returned without matching.

Summary

<code class="language-sql">SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;</code>

Both methods have the same results, providing each unique ID with a row with a maximum Rev value. The choice of two methods depends on performance considerations and readability. It is recommended to perform the benchmark test to determine the best way to determine the specific database and data set.

The above is the detailed content of How to Select Only Rows with the Maximum Value in a Specific Column in SQL?. For more information, please follow other related articles on the PHP Chinese website!