MYSQL/PHP - Select all columns from table records distincting by one column

Question

I'm a bit confused about DISTINCT keyword. Let's guess that this query will get all the records distincting the columns set in the query:

$query = "SELECT DISTINCT name FROM people";

Now, that query is fetching all the records distincting column "name" and at the same time only fetching "name" column. How I'm supposed to ONLY distinct one column and at the same time get all the desired columns?

This would be the scheme:

NEEDED COLUMNS

name
surname
age

DISTINCTING COLUMNS

name

What would be the correct sintaxis for that query? Thanks in advance.

DISTINCT is distinct across all columns you fetch. So if two people have the same name and age, but different surname, you will get two rows. What is the expected result here? If my example happens, which surname do you want to get? — M. Eriksson
– M. Eriksson, Commented Aug 31, 2016 at 11:25
So easy as: SELECT DISTINCT(name), surname, age FROM people ? — Riad
– Riad, Commented Aug 31, 2016 at 11:27
@Riad It's working with static defined columns, but giving mysql error when using "all" selector "*". Ideas? Thank you. — ProtectedVoid
– ProtectedVoid, Commented Aug 31, 2016 at 11:36
@MagnusEriksson The expected result is to get all the records from a table but not duplicated ones by a column. I didn't set the column as a unique key when defined the table because the table itself is used to store duplicated records and then process them. — ProtectedVoid
– ProtectedVoid, Commented Aug 31, 2016 at 11:45
I think you misunderstood my question. If you have two rows: name1, surname1, age1 and name1, surname2, age1, if you then want distinct on firstname, which of the two rows would you expect? The one with surname1 or surname2? And the same question goes in case the age value also differs. — M. Eriksson
– M. Eriksson, Commented Aug 31, 2016 at 12:07

Gordon Linoff · Accepted Answer · 2016-08-31 11:25:34Z

1

If you want one row per name, then a normal method is an aggregation query:

select name, max(surname) as surname, max(age) as age
from t
group by name;

MySQL supports an extension of the group by, which allows you to write a query such as:

select t.*
from t
group by name;

I strongly recommend that you do not use this. It is non-standard and the values come from indeterminate matching rows. There is not even a guarantee that they come from the same row (although they typically do in practice).

Often, you want something like that biggest age. If so, you handle this differently:

select t.*
from t
where t.age = (select max(t2.age) from t t2 where t2.name = t.name);

Note: This doesn't use group by. And, it will return duplicates if there are multiple rows with the same age.

Another method uses variables -- another MySQL-specific feature. But, if you are still learning about select, you should probably wait to learn about variables.

answered Aug 31, 2016 at 11:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ProtectedVoid Over a year ago

Thanks for answering! So DISTINCT keyworkd won't be useful for what I'm looking for, right? If I understood your answer, your recommended technique is to manually group the records by getting the max record for a certain column.

Collectives™ on Stack Overflow

MYSQL/PHP - Select all columns from table records distincting by one column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related