MySQL, Remove duplicate

Question

I need to remove duplicates from my table but MySQL is not working properly

Create table emp
( empID INT(5) PRIMARY KEY,
Pref01 int(1),
Pref02 int(1),
Pref03 int(1),
Pref04 int(1))

empID, Pref01, Pref02, Pref03, Pref04
=====================================
00011    1       2       0       0
00011    1       3       0       0
00022    1       1       0       0
00022    0       3       0       0

I need to keep these records

00011  1       3       0       0 
00022  0       3       0       0 

also I need to keep any record with all pref null value this is my sql:

select empID 
FROM emp 
where max(Pref01) or max (Pref02) or max(Pref03) or max(Pref04) 
    or Pref01 is null or Pref02 is null or Pref03 is null or Pref04 is null 

Answer 1

Your problem is quite complicated, and with given information I have to make an assumption to answer it.. Assume there is no record with same empID have same maximal pref number...

SELECT A.* 
FROM emp AS A 
    INNER JOIN (
        SELECT empID, MAX(GREATEST(Pref01, Pref02, Pref03, Pref04)) AS MaxPref 
        FROM emp GROUP BY empID
    ) AS B ON A.empID = B.empID
WHERE
    (Pref01 = MaxPref OR Pref02 = MaxPref OR Pref03 = MaxPref OR Pref04 = MaxPref)
    OR 
    (Pref01 IS NULL AND Pref02 IS NULL AND Pref03 IS NULL AND Pref04 IS NULL)

If the assumption is not correct then the code will still show duplicate for empID with same max pref number more than one.. to fix it is much more complicated than this code..