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

SQL: Select the line with the maximum value in the column

Consider a simplified document table, which contains column ID, Rev, and Content. To the filter and search for a row containing the maximum Rev value of each ID, you can use the SQL function Max () and Group by.

Extract the maximum value with group by

However, in order to obtain the entire line data containing the maximum Rev value containing each ID, you can use two methods:

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

Method 1: Connect to the maximum value query

Method 2: Use your own conditions to perform a left connection

<code class="language-sql">SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev;</code>

Both methods use left connection and filtering conditions to only retrieve the maximum REV values ​​of each ID. The second method will connect itself to its own left, and use a connection condition to exclude repeated repetition with the maximum REV value. Performance precautions

<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>

The performance of these methods may vary from factors such as database types, table size, and index utilization. It is recommended that these two methods test the benchmark to determine the best solution suitable for your specific scene.

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