How to Find Distinct Rows with Minimum Values in SQL Using Joins?

SQL Query Optimization: Retrieving Unique Rows with Minimum Values

This guide demonstrates an efficient SQL technique to extract unique rows containing the minimum value from a specific column. This is often necessary when dealing with grouped data and requires a combination of joins and filtering.

Consider the following sample table:

<code class="language-sql">id  game   point
1    x      5
1    z      4
2    y      6
3    x      2
3    y      5
3    z      8</code>

The objective is to retrieve the id and point for each unique game, showing the minimum point value for that game. The expected result set is:

<code class="language-sql">id  game   point
1    z      4
2    y      6
3    x      2</code>

The following SQL query achieves this:

<code class="language-sql">SELECT tbl.*
FROM TableName tbl
INNER JOIN (
    SELECT game, MIN(point) AS min_point
    FROM TableName
    GROUP BY game
) AS tbl1 ON tbl1.game = tbl.game AND tbl1.min_point = tbl.point;</code>

Here's a breakdown of the query:

• Inner Join: This efficiently combines the TableName (aliased as tbl) with the results of the subquery (aliased as tbl1).
• Subquery: This calculates the minimum point for each unique game using MIN(point) and groups the results by game. The result is a table with game and its corresponding minimum point.
• ON Clause: The join condition ensures that only rows where the game and point match between the main table and the subquery are included. This precisely selects rows with the minimum point for each game.

This optimized query avoids unnecessary complexity and directly retrieves the desired results. It's a more concise and often faster solution than alternative approaches.

The above is the detailed content of How to Find Distinct Rows with Minimum Values in SQL Using Joins?. For more information, please follow other related articles on the PHP Chinese website!