phpmyadmin Question posted in
The official documentation can be found here.

selecting minimum value depending on other value – Phpmyadmin

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.

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`

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?

enter image description here

Tags:

2

Answers


  1. 0

    Try this:

    SELECT *
FROM hauling AS h
WHERE isBuyOrder = 0
AND price =
  (SELECT MIN(t.price)
   FROM hauling AS t
   WHERE t.isBuyOrder = 0
   AND t.ID = h.ID);
    Login or Signup to reply.
  2. 0

    You don’t need a temporary table at all. You can basically use your current logic:

    SELECT h.*
FROM hauling h
WHERE h.isBuyOrder = 0 AND
      (h.id, h.price) IN (SELECT h2.id, MIN(h2.price)
                          FROM hauling h2
                          WHERE h2.isBuyOrder = 0
                         )

    ORDER BY h.id

    There are many other ways to write similar logic. However, there is no need to rewrite the logic; you just need to include the comparison on isBuyOrder in the subquery.

    Note that not all databases support IN with tuples. If your database does not provide such support, then you would need to rewrite the logic.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search