As you're using DISTINCT here it is applying to all columns as a whole, telling it that info in each of the columns for the rows retrieved need to be distinct. It doesn't parse through each column at a time to find unique entries per column. You are retrieving all rows because it's not finding values where clientid is distinct AND clientname is distinct AND adviceid is distinct in that row.
ex- data set:
x, y, z
x, y, a
x, y, b
c, y, z
None of these three sets are distinct so it gives a result set with all values (even though x is repeated, y is repeated and z shows up as repeats). If x, y, z were listed more than once, it would filter the repeats.
One way to fix this:
(SELECT group_concat(DISTINCT x) FROM my_table) as x,
(SELECT group_concat(DISTINCT y) FROM my_table) as y,
(SELECT group_concat(DISTINCT z) FROM my_table) as z,
Please let me know if this helped!
Originally Posted by gabucknall
I have a table 'advice' which contains various fields including:
clientid, clientname, adviceid and date
I want to get a list out which has everybody who has received advice in it but which only lists the person once no matter how many times they have visited.
I have tried
SELECT DISTINCT clientid, clientname, adviceid FROM advice ORDER BY clientid ASC