Home >Database >Mysql Tutorial >How to Select Distinct Rows with the Minimum Value for a Specific Field in SQL?
Selecting Unique Rows with the Earliest Record Date
Suppose you have a table with multiple entries for each unique identifier, and you need to retrieve only the unique rows representing the earliest record date for each identifier. This is particularly useful when dealing with duplicate record dates.
Here's a SQL query that efficiently solves this problem:
<code class="language-sql">SELECT mt.* FROM MyTable mt INNER JOIN ( SELECT id, MIN(record_date) AS MinDate FROM MyTable GROUP BY id ) t ON mt.id = t.id AND mt.record_date = t.MinDate;</code>
This query works in two steps:
Find Minimum Dates: The inner query (SELECT id, MIN(record_date) AS MinDate FROM MyTable GROUP BY id
) identifies the minimum record_date
for each unique id
.
Select Matching Rows: The outer query joins the results of the inner query with the original table (MyTable
). It selects only the rows from MyTable
where both the id
and record_date
match the minimum date found in the inner query.
The final result contains only the rows with the earliest record_date
for each unique identifier, eliminating duplicate rows with differing record dates.
The above is the detailed content of How to Select Distinct Rows with the Minimum Value for a Specific Field in SQL?. For more information, please follow other related articles on the PHP Chinese website!