How to Select Distinct Rows with the Minimum Value for Each ID and Timestamp?

Group by minimum value and select unique rows

When dealing with data that contains multiple rows for each identifier and timestamp value, it is necessary to group the data by minimum value while selecting unique rows. This ensures that each identifier is represented only once by its earliest occurrence.

In the provided example table, the goal is to extract the row with the smallest record_date for each id. However, the table contains duplicate dates, which makes it difficult to achieve this goal using traditional methods.

This problem can be solved using correlated subqueries and inner joins:

<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 method includes:

1. Create a subquery to identify the smallest record_date for each id.
2. Join the main table (id) with the subquery using the record_date and MyTable fields.

The result table contains only the row with the smallest record_date for each id. This method prevents duplicate rows from appearing in the output.

The above is the detailed content of How to Select Distinct Rows with the Minimum Value for Each ID and Timestamp?. For more information, please follow other related articles on the PHP Chinese website!