CODEWITHSUNDEEP
mysqlsqldistinct
user2327201
user2327201

Reputation: 409

SQL get distinct first name and last name

I have this query here select distinct id, FirstName, LastName from table

This returns me a list of people, first some of the ids were returning duplicates, but I used distinct to fix that, but I still have an issue and that some of the people are duplicates.

Is there also away to put a distinct on the first and last name as well as id? (Some results do not come with an id)

Results (without distinct id):

id - 01 - firstname - james - lastname - smith
id - 01 - firstname - james - lastname - smith
id - 02 - firstname - john - lastname - hicks
id - 02 - firstname - john - lastname - hicks
id -    - firstname - tom - lastname - nicks
id -    - firstname - tom - lastname - nicks

Expecting:

id - 01 - firstname - james - lastname - smith
id - 02 - firstname - john - lastname - hicks
id -    - firstname - tom - lastname - nicks

Upvotes: 1

Views: 16196

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You have duplicates because you are including id in the select:

select distinct FirstName, LastName
from table;

If you need an id for a name, then use group by:

select min(id) as id, FirstName, LastName
from table
group by FirstName, LastName;

EDIT:

If you are still getting duplicates with these queries, then you have characters in the names that are throwing things off. I would start by trimming leading and trailing spaces to see if this fixes the duplicates problem:

select min(id) as id, trim(FirstName) as FirstName, trim(LastName) as LastName
from table
group by trim(FirstName), trim(LastName);

Upvotes: 6

Related Questions