0

I have an order table as follows:

CREATE TABLE `order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `status` VARCHAR(10) NOT NULL,
    `date_created` DATETIME
)

I am trying to select all first orders made by each user, and check if their status is cancelled. I have tried the following SQL:

SELECT MIN(o.created_at), o.user_id, o.status FROM `order` o GROUP BY o.user_id;

SELECT MIN(o.created_at), o.user_id, o.status FROM `order` o GROUP BY o.user_id HAVING o.status = 107;

Both give me the following error: "Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'o.status' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

Is it possible to do this query without removing the sql_mode only_full_group_by?

Bug Magnet
  • 2,668
  • 5
  • 29
  • 28
  • I know that the 2nd duplicate is for max, not for min, but those techniques can be easily changed to work with min instead. – Shadow Nov 03 '19 at 08:22
  • 1
    Also, as you've spotted, order is a reserved word making it (at least in my view) an exceptionally poor choice as a table identifier – Strawberry Nov 03 '19 at 10:53

0 Answers0