How can I select only the first distinct match from a field in MySQL?

Question

How can I only return the first distinct match of a field in MySQL? My Table: ``` name hash ---------------- Anna ABC Barb DEF Charlie GHI Anna JKL Andrea MNO ``` My Query (for `%An%`) : ``` SELECT DISTINCT(name) as name, hash FROM my_table WHERE name LIKE '%An%'; ``` This returns: ``` name hash ---------------- Anna ABC Anna JKL Andrea MNO ``` Instead of: (the result I'm after) ``` name hash ---------------- Anna ABC Andrea MNO ``` How can I get only the first match of each distinct name? I want to return the first `Anna`, skip the second (and any subsequent matches), but still get `Andrea` (and any further distinct matches, like `Andrew` or `Anthony`).

Accepted Answer

`DISTINCT` does not work that way, the values must be distinct across all columns being returned.

You can always use an aggregate function on the `hash` function and `GROUP BY name` which will return one `hash` value for each `name`:

```
SELECT name, min(hash) hash
FROM my_table 
WHERE name LIKE '%An%' 
GROUP BY name;
```

See SQL Fiddle with Demo.

Note: using the aggregate function with the `GROUP BY` will make sure that you will always return the expected value for the `hash` column. When you do not `GROUP BY` or aggregate the items in the `SELECT` list, you might return unexpected results. (see MySQL Extensions to `GROUP BY`)

From the MySQL Docs:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. ... You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

How can I select only the first distinct match from a field in MySQL?

Solution

Problem