sql - MySQL remove duplicates in one column, leaving smallest and greatest values based on second column -

i have table contains 2 columns:

count | when 101   | 10:11 101   | 10:12 102   | 10:13 102   | 10:14 102   | 10:15 102   | 10:16 103   | 10:17 105   | 10:18 105   | 10:19 105   | 10:20 

i need remove duplicate count entries, leave 2 rows smallest , largest when value:

count | when 101   | 10:11 101   | 10:12 102   | 10:13 _ removed 2 rows 102   | 10:16 103   | 10:17 105   | 10:18 _ removed 1 row 105   | 10:20 

this in order minimize table size, used statistical purpose, hence points same y-axis values irrelevant.

mysql supports join syntax delete command.

using syntax, can set filter appropriate records delete:

delete t     yourtable t join          (select count, min(when) minwhen, max(when() maxwhen           yourtable           group count          ) tokeep          on t.count = tokeep.count , (t.when > tokeep.minwhen , t.when < tokeep.maxwhen);