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

SQL Select the maximum line in the column: complete guide

Problem description:

Considering the following table containing document information:

id	rev	content
1	1	...
2	1	...
1	2	...
1	3	...

The goal is to retrieve one line for each ID, which only contains documents with a maximum revision number (REV). The expected output of the given data is:

id	rev	content
1	3	...
2	1	...

This problem usually appears in SQL, prompting users to find the maximum values ​​in the columns of the given group identifier.

Solution 1: Use the sub -query connection

The first method involves the creation of a sub -query, the maximum value in the identifier (ID) and the REV column of the sub -query identification group. You can use the group identifier and equal conditions on the maximum value to connect this child query with the main watch.

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

Solution 2: It is connected to the left

The second method uses the left connection operation to be associated with its own. Specify equal conditions on the bidding identity (ID). In order to ensure that only the rows with the maximum Rev value are selected, a condition is added to check whether the right REV value is less than the left REV value. Finally, the application filter uses a row with only the REV value on the right (indicating the maximum Rev value).

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

Conclusion:

Both solutions have the same results and are compatible with SQL ANSI. The most suitable choice depends on personal preferences and specific database performance characteristics. It is recommended to perform the benchmark test to determine the best way in different scenarios.

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