How to Retrieve Distinct Rows with the Minimum Value in SQL?

Extracting Unique Rows with Minimum Values Using SQL

SQL queries frequently involve selecting specific rows based on defined conditions. A common scenario is retrieving the row with the minimum value in a column, while ensuring that only unique rows are returned.

Let's illustrate with this sample table:

<code>id | game | point
-------+--------+--------
1  | x    | 5
1  | z    | 4
2  | y    | 6
3  | x    | 2
3  | y    | 5
3  | z    | 8</code>

To obtain the id with the lowest point value for each game, we can employ a subquery and an inner join. The subquery identifies the minimum point for each id:

<code class="language-sql">SELECT Id, MIN(Point) AS MinPoint
FROM TableName
GROUP BY Id</code>

This subquery yields ids and their respective minimum point values. The main query then joins this result with the original table, filtering for rows matching the minimum points:

<code class="language-sql">SELECT tbl.*
FROM TableName tbl
INNER JOIN (
    SELECT Id, MIN(Point) AS MinPoint
    FROM TableName
    GROUP BY Id
) tbl1 ON tbl1.id = tbl.id
WHERE tbl1.MinPoint = tbl.Point;</code>

The query's output:

<code>id | game | point
-------+--------+--------
1  | z    | 4
3  | x    | 2
2  | y    | 6</code>

This method effectively returns unique rows containing the minimum point value for each id, providing a clean dataset for subsequent analysis.

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