How to Retrieve Rows with Minimum Values for Each Unique Identifier in SQL?

SQL efficient screening: Get the minimum value of each unique identifier

In database management, it is often necessary to extract records with unique values ​​and its corresponding minimum values. Suppose there is a form containing the unique identifier column and associated values. The goal is to retrieve the rows corresponding to the minimum value from each unique identifier.

Challenge

Consider the following table:

The goal is to find the minimum

line corresponding to each unique

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

value. The expected output is as follows:

game Solution point

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

You can use the following SQL query to achieve this:

Explanation

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;

Internal query calculates the minimum

value of each unique and stores the result in the derivative table .

The main inquiry will connect the original table
• with Id according to the public Point column. tbl1
In the end, it filter the result, only contains the minimum
• value (from Id) and the line of TableName in the original table. tbl1
• By using this query, you can efficiently retrieve the unique row of Point in the tbl1 group. Point

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