How to Select All Columns with DISTINCT in MySQL?

MySQL: Understanding DISTINCT for Selecting Multiple Columns

When dealing with duplicate rows in MySQL tables, the DISTINCT keyword can be used to retrieve unique values, but it can also pose limitations when selecting multiple columns. This article addresses the issue of retrieving all fields while using DISTINCT, along with a solution to select the latest entry for duplicate rows.

Limitations of DISTINCT for Multiple Columns

DISTINCT is not a function that applies only to specific columns. Instead, it's a query modifier that affects all columns in the select list. It removes duplicate rows where the values of all columns are identical.

Correct Syntax for DISTINCT

DISTINCT must appear immediately after SELECT (along with other query modifiers, such as SQL_CALC_FOUND_ROWS). Following the query modifiers, the column names should be listed.

For example:

<code class="sql">SELECT DISTINCT foo, ticket_id FROM table...</code>

This query will output a row for each distinct pairing of values across ticket_id and foo.

Solution for Selecting All Columns

It's not possible to select all fields and still use DISTINCT because it requires all columns to be identical for duplicate removal. However, there's a workaround that can be used to select the latest entry for duplicate rows:

<code class="sql">SELECT t1.*
FROM table AS t1
JOIN (
    SELECT ticket_id, MAX(row_id) AS max_id
    FROM table
    GROUP BY ticket_id
) AS t2 ON t1.ticket_id = t2.ticket_id AND t1.row_id = t2.max_id</code>

This query uses a subquery to find the maximum row_id for each ticket_id, and then joins the main table with the subquery to select the latest row for each ticket_id.

The above is the detailed content of How to Select All Columns with DISTINCT in MySQL?. For more information, please follow other related articles on the PHP Chinese website!