Distinct is not working in crystal reports and in mysql

Question

id_no    doc_id    item_no   product    customer
123        2         1         A        Daisy
123        2         9         A        Ben
123        4         3         A        Daisy
123        4         4         A        Ben
123        6        11         B        Daisy
123        6        13         B        Ben

when I put it in my report it results to

Daisy     Daisy   
Ben

And it is also the result in mysql

select distinct customer from receipt where id_no like '123'

result:
Daisy
Daisy        
Ben

Another query that I tried:

select distinct id_no, customer, product from receipt where id_no like '123'

result:
123       Daisy   A
123       Daisy   B
123       Daisy   A
123       Ben     A
123       Ben     B

desired result:
Daisy
Ben

Please help me please.

Thank you guys for the help I found out why the other one keeps on showing. It is because the other Daisy is spelled as Daissy that's why.

Answer 1

Most likely your Customer name contains additional characters between the two records. Depending on how the datatype is implemented, spaces could matter and have contributed to the difference. Try concatenating a character before and after customer.

I am unfamiliar with the concepts in Crystal Reports, but from what I understand, you would have to create a formula like so:

"XXX" & {Receipt.Customer} & "XXX" 

If you run it again, you might recognize there is additional space like so:

XXXDaisyXXX
XXXDaisy XXX
        ^____ Additional Space