MySQL: select all rows where just the name is distinct

Question

I'm currently trying to select unique entries in only the name column. I have tried using this query but it will not return prices that are the same as well. I've tried other variations with no success either.

SELECT DISTINCT name, price from table;

Here's the table I'm working with:

+----+-------------------+
| id | name      | price |
+----+-----------+-------+
| 1  | Henry     | 20    |
| 2  | Henry     | 30    |
| 3  | Robert    | 20    |
| 4  | Joshua    | 10    |
| 5  | Alexander | 30    |
+----+-----------+-------+

The output that I'm seeking is:

+----+-------------------+
| id | name      | price |
+----+-----------+-------+
| 1  | Henry     | 20    |
| 3  | Robert    | 20    |
| 4  | Joshua    | 10    |
| 5  | Alexander | 30    |
+----+-----------+-------+

The desired output as you can tell only removed the duplicate name and none of the prices. Is there something I can add to my query above to only select unique entries in the name column? Any help is really appreciated as I have tried to find a solution on here, Google, DuckDuckGo, etc. with no luck.

Do you care which row is returned? Which ID/price pair? – Lightness Races in Orbit Oct 23 '18 at 21:04 — Lightness Races in Orbit, Oct 23 '18 at 21:04

score 0 · Answer 1 · answered Oct 23 '18 at 21:05

0

From your sample data, this should work.

SELECT MIN(Id) AS Id, name, MIN(price) AS price 
FROM table
GROUP BY name;

answered Oct 23 '18 at 21:05

Eric

3,165
1
19
25

1

The result of this is going to be extremely misleading (i.e. wrong) if the row with the lowest number does not have the lowest price. I don't recommend it. – Lightness Races in Orbit Oct 23 '18 at 21:07

score 0 · Answer 2 · answered Oct 23 '18 at 21:05

This is what GROUP BY is for:

SELECT * FROM `table` GROUP BY `name`

Usually people run into trouble because they will now get an arbitrarily-chosen row when more than one matches for a given name — you have to use aggregate functions to pick a specific one, e.g. "the one with the maximum price".

But in your case, since you don't seem to care which row is returned, this is perfect as-is.

score 0 · Answer 3 · answered Oct 23 '18 at 21:13

So you want to select distinct list of rows AND then select that given entire row from the table? Try this query where temporary query is just a list of uniqueid then that row is linked back to the table.

Select n.*
From nameprices n
Join (Select MIN(id) as id
  From nameprices
  Group by name
  Order By id) aTemp On (aTemp.id=n.id);

This is a common problem in SQL queries where we want to use that given fully row data but filter was using a distinct/groupby formula.

MySQL: select all rows where just the name is distinct

3 Answers3