How to Select Unique Rows with the Minimum Value in SQL?

How to select the unique row with minimum value in SQL query?

In databases, it is often necessary to extract the records containing the minimum value of a specific column. Suppose a table contains columns 'id', 'game' and 'point'. To select the only rows with the smallest 'point' value (grouped by 'game'), you can use the following valid SQL query:

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

This query ensures that only the rows with the smallest 'point' value for each unique 'id' are selected. The subquery inside the brackets calculates the minimum 'point' value for each 'id' and stores it in the temporary column 'MinPoint'. The main query then joins the original table ('TableName') with this temporary table based on the common column 'id'.

By filtering the condition 'tbl1.MinPoint = tbl.Point', the query ensures that only rows whose 'point' value matches the minimum value within each 'id' group are returned. This will produce the desired output, where for each 'game' the unique row with the smallest 'point' value is selected.

Example:

Consider the following form:

id	game	point
1	x	5
1	z	4
2	y	6
3	x	2
3	y	5
3	z	8

Executing the above query on this table will produce the following output:

id	game	point
1	z	4
2	y	6
3	x	2

As you can see, the query correctly identifies the unique row with the smallest 'point' value for each 'game'.

Note: There are inconsistencies between the original sample data and the results. The result table has been corrected to make it consistent with the SQL statement results. The corrected results table shows the row with the smallest id value for each point. If you need to select the smallest game value by point grouping, you need to modify the SQL statement.

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