MySQL select * with distinct id

MySQL select * with distinct id

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

The Syntax Trap of SELECT * and DISTINCT

In SQL, combining SELECT * with DISTINCT is a common point of confusion. You might be looking for rows where the primary key or a specific identifier is unique, while still retrieving every column from the database. The immediate instinct is often to write something like SELECT * DISTINCT(id) FROM table. This syntax, however, is invalid in standard SQL and will cause your query to fail immediately.

The core issue lies in how the SQL engine parses the DISTINCT keyword. When you place DISTINCT after the asterisk (*), the parser expects it to apply to the entire list of columns following it. Conversely, placing an identifier like (id) inside parentheses before DISTINCT creates a syntax error because DISTINCT cannot operate on a sub-selection or a single column in that position within a standard SELECT clause.

To achieve your goal—returning unique IDs while fetching all other fields—you must restructure the query to explicitly list the columns you need, placing DISTINCT immediately after the column list and before any WHERE, GROUP BY, or ORDER BY clauses. The correct syntax is SELECT DISTINCT id, city, state, zip FROM table. By removing the asterisk and listing your specific fields, you signal to the database engine exactly which rows to deduplicate based on the id column.

Why Syntax Matters Here

The reason your previous attempts failed stems from the strict parsing rules of SQL. When you write SELECT * DISTINCT(ID), the engine sees an asterisk followed by a keyword it doesn't expect in that specific order, or it interprets DISTINCT(id) as a function call rather than a set operator. Similarly, SELECT DISTINCT ID * is ambiguous and often rejected because the asterisk implies "all remaining columns," but DISTINCT requires a defined set of columns to compare against to determine uniqueness.

In your specific case, where you need the ID, City, State, and Zip, the most efficient approach is to list those four columns explicitly. This allows the database optimizer to choose the most efficient index scan for the id column while ensuring it retrieves the exact data required without pulling in unnecessary columns from a wide table.

Handling Data Integrity with Duplicate IDs

There is a deeper logical question to consider here: what does it mean to have duplicate IDs? If your primary key (usually id) is truly unique, you should not see duplicates at all unless there is data corruption or multiple tables involved in an unjoined query. If this table acts as a junction table or a log where non-unique IDs are expected, then using DISTINCT id is the correct safety mechanism to filter out accidental redundancy.

If your application logic relies on mysql_fetch_array, returning rows with duplicate IDs could lead to data inconsistency in your downstream processes. By enforcing uniqueness on the identifier level, you ensure that even if the underlying data contains transient duplicates, your query results remain clean and predictable.

Performance Considerations

While fixing the syntax solves the immediate error, performance remains a critical factor. If you frequently need to fetch unique records based on an ID, consider creating an index on the id column if one does not already exist. An indexed column allows the database to quickly locate and verify uniqueness without scanning the entire table row by row.

If you find yourself needing to select all columns but ensure uniqueness across multiple columns (e.g., DISTINCT id, city), remember that SELECT * combined with DISTINCT can sometimes be more expensive than listing specific columns. The database must calculate the hash or sort order for every single column to determine uniqueness, which consumes more CPU and memory. Explicitly listing the necessary fields, as shown in SELECT DISTINCT id, city, state, zip, is generally faster and safer for your application's performance profile.

Our network