title: selecting minimum value depending on other value
body: <p>Is there any better way for below sql query? Don't want to drop and create temporary table just would like to do it in 1 query.
  I am trying to select minimum value for price depending if its order sell where obviously price is higher then in buy and it just shows 0 results when I try it.</p>
  
  <pre><code>DROP TABLE `#temporary_table`;
  CREATE TABLE `#temporary_table` (ID int(11),region int(11),nazwa varchar(100),price float,isBuyOrder int,volumeRemain int,locationID int,locationName varchar(100),systemID int,security_status decimal(1,1));
  INSERT INTO `#temporary_table` SELECT * FROM hauling WHERE isBuyOrder=0 ORDER BY ID;
  SELECT * FROM `#temporary_table`;
  SELECT * FROM `#temporary_table` WHERE (ID,price) IN (select ID, MIN(price) from `#temporary_table` group by ID) order by `ID`
  </code></pre>
  
  <p>UPDATE: when I try nvogel answer and checked profiling thats what I get:
  Any chance to optimize this or different working way with 700k rows database?</p>
  
  <p><a href="https://i.stack.imgur.com/iHB0B.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iHB0B.png" alt="enter image description here"></a></p>