Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2014
    Posts
    35

    Unanswered: DISTINCT not working

    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

    but this doesn't remove duplicates from the list.

    What am I do wrong?

    Many thanks

  2. #2
    Join Date
    Jan 2012
    Posts
    20
    select distinct clientid from advice where adviceid is not null order by clientid asc;

  3. #3
    Join Date
    Dec 2014
    Posts
    3
    Hi there,
    Which duplicate does not get removed?
    -clientid
    Or
    -clientname

    If it helps, you can put select distinct (clientid)

  4. #4
    Join Date
    Dec 2014
    Posts
    3

    Red face Using DISTINCT

    Maybe you've already figured this out...

    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!

    Quote Originally Posted by gabucknall View Post
    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

    but this doesn't remove duplicates from the list.

    What am I do wrong?

    Many thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •