SQL Select Only Rows with Max Value on a Column

Mysql Sql Aggregate Functions Greatest N Per Group Groupwise Maximum

In this article, we will discuss how to select only the rows with the maximum value on a column in a SQL database. This problem is commonly encountered when dealing with tables that have multiple entries for the same identifier and you want to retrieve only the row with the highest or maximum value for a specific column.

Problem Overview

Let's consider the following simplified table of documents:


        +----+---------+-----+
        | id |  doc    | rev |
        +----+---------+-----+
        | 1  | Doc1    | 1   |
        | 1  | Doc1    | 2   |
        | 1  | Doc1    | 3   |
        | 2  | Doc2    | 1   |
        | 2  | Doc2    | 2   |
        +----+---------+-----+

The objective is to select only one row per id and only the row with the greatest rev (revision).

Solution using a Subquery

To solve this problem, we can use a subquery to find the maximum rev for each id, and then join the original table with this subquery to filter out the rows that do not have the maximum rev.


        SELECT t1.id, t1.doc, t1.rev
        FROM documents t1
        JOIN (
            SELECT id, MAX(rev) AS max_rev
            FROM documents
            GROUP BY id
        ) t2 ON t1.id = t2.id AND t1.rev = t2.max_rev;

The subquery selects the id and the maximum rev for each id from the documents table. This subquery is then joined with the original table using the id and rev columns as the join condition. Only the rows where the id and rev match between the two tables are selected.

Using the example table given above, the result of this query would be:


        +----+---------+-----+
        | id |  doc    | rev |
        +----+---------+-----+
        | 1  | Doc1    | 3   |
        | 2  | Doc2    | 2   |
        +----+---------+-----+

Alternative Solution using RANK() Window Function

Another approach to solving this problem is by using the RANK() window function. This function assigns a rank to each row within a partition based on the specified ordering. We can use this function to rank the rows within each id partition by the rev column in descending order and then select only the rows with rank 1.


        SELECT id, doc, rev
        FROM (
            SELECT id, doc, rev, RANK() OVER (PARTITION BY id ORDER BY rev DESC) AS rank
            FROM documents
        ) ranked_docs
        WHERE rank = 1;

This query first assigns a rank to each row within each id partition based on the rev column in descending order. The row with the highest rev in each partition will have a rank of 1. Finally, the outer query selects only the rows with rank 1.

The result of this query using our example table would be the same as the previous query:


        +----+---------+-----+
        | id |  doc    | rev |
        +----+---------+-----+
        | 1  | Doc1    | 3   |
        | 2  | Doc2    | 2   |
        +----+---------+-----+

Conclusion

In this article, we explored how to select only the rows with the maximum value on a column in a SQL database. We discussed two different solutions to this problem using subqueries and the RANK() window function.

By using these SQL techniques, you can easily retrieve the desired results and filter out the rows that do not have the maximum value on a specific column. Whether you choose to use a subquery or the RANK() function depends on your specific requirements and the database system you are using.

Related Questions