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:

1. 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.

2. 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!