asked on

Help with opposite of DISTINCT in mysql

Hi There,

I have a problem where I have a database table with duplicate records in it.
What I want to do is change the field "searchableFoodItem" to false if it is a duplicate record.

Please note the count on some duplicates is 19 or more.

I want to always keep the first record of the duplicates set as true but every other duplicate I want as false.

Here is my query which I am using which shows the counts.

Any help would be appreciated.

Thanks in advance,
George

select item, carbohydrate, quantityServe, protein, fat, searchableFoodItem, count(*) as count
from myTable
group by item
having count > 1

Open in new window

George-TCC

ASKER

bump - any ideas?

nasirbest

With a variable you can compare food item if matched then false. if do not. save food item value into this variable so next time if same food item repeat compare will be found and function return false. and remember to sort query by food item otherwise it will not work as excepted.

SET @food_item:=0;

SELECT item, 
            carbohydrate, 
            quantityServe, 
            protein, 
            fat, 
            IF(searchableFoodItem=@food_item, false, @food_item:=searchableFoodItem) AS searchableFoodItem, 
            count(*) as count
FROM myTable
ORDER BY searchableFoodItem

Open in new window

ASKER CERTIFIED SOLUTION

theGhost_k8

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

theGhost_k8

Sorry, make it TRUE.
That will make all the first records of duplicate as true.

DangItMarilyn

You asked for more answers, but you haven't replied to the other answers here. Did you attempt them? Did they fail and in what way?

George-TCC

ASKER

Yes I tried those answers but found my solution by my own trial and error over the days and resolved it myself. The way the failed was but not displaying the results at all in my query

DangItMarilyn

Ok, great! Glad you got it figured out. You should close the question by choosing an answer or request it closed :)

It's popping up on the "omg answer me now!!!" alerts