Group BY DESC being ignored after join - Mysql 5

The problem I wish to solve

I am trying to select the trucks that are on duty. I tried using sub queries. However, I heard that it has bad performance. I also tried adding a is_onduty flag to the truck table, but I feel that it could cause potential issues should the application crash.

tl;dr:
I wish to join 2 tables by a foreign key and select
the distinct records of the foreign key

fiddle

select version();

version()

5.7.39

CREATE TABLE IF NOT EXISTS `truck` (
  `truck_id` varchar(12) NOT NULL,    # license plate
  `is_online` tinyint(1) DEFAULT '0',
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`truck_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO truck(truck_id) VALUES 
  ("ABC123"),
  ("DEF123"),
  ("ADF134");

Records: 3  Duplicates: 0  Warnings: 0

SELECT * FROM truck

truck_id
is_online
deleted_at

ABC123
0
null

ADF134
0
null

DEF123
0
null

CREATE TABLE IF NOT EXISTS `consignment` (
  `consignment_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `shipment` varchar(255) NOT NULL,
  `transporter` varchar(255) NOT NULL,
  `truck_id` varchar(12) NOT NULL,
  PRIMARY KEY (`consignment_id`),
  KEY `consignment_truck_id_index` (`truck_id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4;

INSERT INTO consignment(shipment, transporter, truck_id) VALUES 
  ("shipment1", "transporter1", "ABC123"), 
  ("shipment2", "transporter2", "ABC123"), 
  ("shipment3", "transporter3", "DEF123"),
  ("shipment4", "transporter4", "DEF123"),
  ("shipment5", "transporter5", "DEF123"),
  ("shipment6", "transporter6", "DEF123");

Records: 6  Duplicates: 0  Warnings: 0

SELECT * FROM consignment

consignment_id
shipment
transporter
truck_id

30
shipment1
transporter1
ABC123

31
shipment2
transporter2
ABC123

32
shipment3
transporter3
DEF123

33
shipment4
transporter4
DEF123

34
shipment5
transporter5
DEF123

35
shipment6
transporter6
DEF123

```
SELECT c., t.
FROM consignment a

I tried out sub query and it works! However, I am a bit reluctant to
use it in production as I heard that it is bad for performance

In your case you have to use a subquery to get latest consignment_id , with the correct indexes which it seems you have it it will not be performance bad.

SELECT c.*, t.*
FROM consignment as c
JOIN  truck as t ON c.`truck_id` = t.truck_id
inner join (select max(consignment_id) as consignment_id,
                   truck_id
            from consignment
            group by truck_id 
           ) as max_id on max_id.consignment_id=c.consignment_id   ;

https://dbfiddle.uk/OVMCrrxM

However, only_full_group_by seemed to caused some error

ONLY_FULL_GROUP_BY.

If ONLY_FULL_GROUP_BY is disabled, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want.

StackExchange Database Administrators Q#320143, answer score: 3