Concatenate rows & then get distinct values

Question

I have the following columns in my table: alias, first, last. I would like to concatenate the rows & then search for distinct values: i.e.

1. jimmyWho, Jim, Smith
2. BallyHo, Bob, Smith
3. JimmytwoShoes, Jim, Smith
4. Bobtastic, Bob, Johnson
5. JimmytwoShoes, Jim, Smith
6. BallyHo, Dave, Jones

I would like to have the following results (notice that #5 above is a duplicate):

1. jimmyWho, Jim, Smith
2. BallyHo, Bob, Smith
3. JimmytwoShoes, Jim, Smith
4. Bobtastic, Bob, Johnson
5. BallyHo, Dave, Jones

In other words, I need to concatenate the rows & then search for distinct values only AFTER I've concatenated...doing so b/f the concatenation would not give the desired results. Is there a way to do this in Mysql?

Answer 1

Check out DISTINCT

SELECT DISTINCT alias, first, last FROM users;

I don't understand your reason behind the concatenation. However, if the above doesn't work you can concatenate the records with CONCAT()

SELECT DISTINCT CONCAT(alias, ', ', first, ', ', last) FROM users;

Answer 2

I'm not sure this is entirely applicable to your situation but you should read up on the group_concat function to see if it meets your needs. The query might look like

select distinct group_concat(column) from table where whaterver group by commonColumn.