sql: keep only the minimum value if two rows have duplicate id

Question

Below is my sample data. Row 3 and 4 have the same st_case (the primary key), but their dist_min are different. I want to keep the row with the minimum dist_min value. And please notice that there could be more than 2 duplicate rows associate with the same st_case. Thank you so much for the help!

This is a little confusing. You say at the beginning of your issue that st_case is the primary key. Then later you say there are duplicate values for st_case. How can you have duplicate values for your primary key? — Sam M, Jul 25 '15 at 01:46
you need to fetch data form this table with minimum dist_min or delete those rows that have not minimum dist_min? — M0rtiis, Jul 25 '15 at 03:00

score 3 · Accepted Answer · answered Jul 25 '15 at 01:48

In MySQL, you can do this with a delete and join:

delete s
    from sample s left join
         (select st_case, min(dist_min) as mindm
          from sample s
          group by st_case
         ) ss
         on ss.st_case = s.st_case and s.dist_min > ss.mindm;

score 3 · Answer 2 · answered Jul 25 '15 at 04:05

3

You can try this one:

DELETE t1 FROM table AS t1
LEFT JOIN table t2 ON t1.st_case = t2.st_case 
WHERE t1.dist_min > t2.dist_min

answered Jul 25 '15 at 04:05

Wilianto Indrawan

2,394
3
21
26

Ricardo Meneghin Filho · Answer 3 · 2015-07-25T04:22:48.213

2

DELETE FROM sample 
WHERE dist_min !=(SELECT dist_min FROM (SELECT * FROM sample) sample2 
                  WHERE sample2.st_case = sample.st_case 
                  ORDER BY dist_min 
                  LIMIT 1)

edited Jul 25 '15 at 04:22

answered Jul 25 '15 at 02:44

Ricardo Meneghin Filho

414
3
13

This one will not work in mysql, because mysql does not allow the row you are deleting to use in subquery. You will get error `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versio....` – Wilianto Indrawan Jul 25 '15 at 03:59
I edited my answer to correct that. The error was actually "Error Code: 1093. You can't specify target table 'a' for update in FROM clause" – Ricardo Meneghin Filho Jul 25 '15 at 04:23

score 2 · Answer 4 · edited Jul 25 '15 at 04:09

As SAM M suggested, I am not sure how you can have duplicate rows with same primary key. However in case st_case is not your only key and the table has a composite key OR had it been a non-key column, You could write a trigger to control the insertion Something like:

CREATE table CALC_STATUS(id varchar(40), correlatoinToken integer, requirement double)

CREATE TRIGGER keep_min BEFORE INSERT ON CALC_STATUS 
    FOR EACH row 
BEGIN 
    IF NEW.correlatoinToken = correlatoinToken AND NEW.requirement <= requirement then
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "A row with similar correlation token with lower requirement already exists";
ELSEIF NEW.correlatoinToken = correlatoinToken AND NEW.requirement > requirement
    NEW.requirement = requirement;
END IF;
END;

And if you meant to query:

SELECT c1.* FROM CALC_STATUS c1,(SELECT st_case,MIN(dist_min) FROM CALC_STATUS GROUP BY road_id) AS c2 WHERE c1.st_case=c2.st_case

Nate May · Answer 5 · 2015-07-25T02:14:39.673

1

I didn't get to test this so please forgive any syntax errors. SQL ranking functions can solve your problem. Essentially you group by one column and then rank by another. Then you can select only those of rank one.

SELECT * 
FROM(
  SELECT *, Rank() OVER (PARTITION BY 'st_case' Order by Dist_min DESC) as Rank
  From 'tbl_Name')
WHERE Rank = '1'

edited Jul 25 '15 at 02:14

answered Jul 25 '15 at 02:09

Nate May

3,814
7
33
86

sql: keep only the minimum value if two rows have duplicate id

5 Answers5

And if you meant to query: