title: mysql - how to delete records with condition
body: <p>Here is my table structure: </p>
  
  <pre><code>CREATE TABLE `order`
      (`order_id` int, `order_status_id` int, `ip` varchar(11), `date_added` datetime)
  ;
  
  INSERT INTO `order`
      (`order_id`, `order_status_id`, `ip`, `date_added`)
  VALUES
      (1, 0, '192.168.1.1', '2016-12-07 00:00:00'),
      (2, 0, '192.168.1.1', '2016-12-07 00:00:00'),
      (3, 0, '192.168.1.1', '2016-12-07 00:00:00'),
      (4, 0, '192.168.1.1', '2016-12-07 00:00:00'),
      (5, 1, '192.168.1.1', '2016-12-07 00:00:00'),
  
      (6, 0, '192.168.1.2', '2016-12-08 00:00:00'),
      (7, 0, '192.168.1.2', '2016-12-08 00:00:00'),
      (8, 0, '192.168.1.2', '2016-12-08 00:00:00'),
      (9, 0, '192.168.1.2', '2016-12-08 00:00:00'),
      (10, 1, '192.168.1.2', '2016-12-08 00:00:00'),
  
      (11, 0, '192.168.1.3', '2016-12-09 00:00:00'),
      (12, 0, '192.168.1.3', '2016-12-09 00:00:00'),
      (13, 0, '192.168.1.3', '2016-12-09 00:00:00'),
      (14, 0, '192.168.1.3', '2016-12-09 00:00:00'),
      (15, 0, '192.168.1.3', '2016-12-09 00:00:00');
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!9/20c0f" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/20c0f</a></p>
  
  <p>I expect sql erase all records except records where order_id are: 5, 10, 15</p>
  
  <p>Explanations: I would like to erase records which has order_status_id = 0 with conditions:</p>
  
  <p>a) if one of the record (from same ip/date_added order_id) has order_status_id = 1, then erase all records which has order_status_id = 0 (from same ip/date_added).  In my example records 1-4 and 6-9 should be deleted. </p>
  
  <p>b) if there are no record from same ip/date_added with order_id = 1 (all records has order_status_id = 0), then leave one record with the highest order_id (all other deleted). In my example records 11-14 should be deleted. </p>