How to Select All Fields with DISTINCT in MySQL?

Unable to Select All Fields with DISTINCT in MySQL Query

When attempting to eliminate duplicate rows using the DISTINCT keyword, users may encounter limitations in selecting all fields in their MySQL queries. This article addresses the issue and provides a solution.

Background

The DISTINCT keyword identifies and returns only unique rows in a result set, based on the specified column(s). However, it can lead to errors when combined with a select-all statement (*).

Consider the following query, which selects only the ticket_id column in the temp_tickets table and orders it by ticket_id:

mysql_query("SELECT DISTINCT ticket_id FROM temp_tickets ORDER BY ticket_id")

This query will execute successfully and return distinct ticket_id values. However, the following query, which attempts to select all fields and use DISTINCT:

mysql_query("SELECT * , DISTINCT ticket_id FROM temp_tickets ORDER BY ticket_id")

Will result in an error. This is because DISTINCT can only be applied to a single column.

Solution

To select all fields and use DISTINCT to eliminate duplicates, modify the query as follows:

SELECT ticket_id, <other_field>, <other_field> FROM temp_tickets GROUP BY ticket_id ORDER BY ticket_id

In this query, the GROUP BY clause is used in conjunction with DISTINCT to group the results by the ticket_id column. For each unique ticket_id, only a single row will be returned, containing the latest values for the specified fields.

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