Sort orders by status-CodePudding

I need to sort the table with the product_id that had the most status "FINISHED"

Table:

CREATE TABLE order2 (
  id         int NOT NULL AUTO_INCREMENT,
  status     enum('PENDING','FINISHED') NOT NULL DEFAULT 'PENDING',
  note       varchar(255) DEFAULT NULL,
  client_id  int DEFAULT NULL,
  product_id int NOT NULL,

  PRIMARY KEY (id),  
  KEY fk_order_client2_idx (client_id),
  KEY fk_order_product2_idx (product_id),
  
  CONSTRAINT fk_order_client2  FOREIGN KEY (client_id)  REFERENCES client (id)  ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT fk_order_product2 FOREIGN KEY (product_id) REFERENCES product (id) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

I tried a few ways but i didn't succeed...

CodePudding user response：

give it a go, isn't order a word that should not by used for tables? not 100% sure:

    select * from order where status='FINISHED' order by product_id desc;

CodePudding user response：

You need some kind of aggregagion by product here. Assuming you are using MySQL 8 , you may use SUM() as a window function:

SELECT *
FROM order2
ORDER BY SUM(status = 'FINISHED') OVER (PARTITION BY product_id) DESC;

On earlier versions of MySQL, you may use a join with aggregation approach:

SELECT o2a.*
FROM order2 o2a
LEFT JOIN
(
    SELECT product_id, COUNT(*) AS cnt
    FROM order2
    WHERE status = 'FINISHED'
    GROUP BY product_id
) o2b
    ON o2b.product_id = o2a.product_id
ORDER BY
    COALESCE(o2b.cnt, 0) DESC;