Concatenate rows & then get distinct values
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.
- jimmyWho, Jim, Smith
- BallyHo, Bob, Smith
- JimmytwoShoes, Jim, Smith
- Bobtastic, Bob, Johnson
- JimmytwoShoes, Jim, Smith
- Bal开发者_高级运维lyHo, Dave, Jones
I would like to have the following results (notice that #5 above is a duplicate):
- jimmyWho, Jim, Smith
- BallyHo, Bob, Smith
- JimmytwoShoes, Jim, Smith
- Bobtastic, Bob, Johnson
- 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?
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;
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.
If I understand what you're asking for, you could do a query similar to this:
SELECT DISTINCT CONCAT(all the columns or whatever) FROM table
See more about select distinct
精彩评论