MariaDB 10.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `cars` (
  `brand` varchar(128) DEFAULT NULL,
  `model` varchar(128) DEFAULT NULL,
  `value` int(11) NOT NULL
);
CREATE TABLE `used_cars` (
  `brand` varchar(128) DEFAULT NULL,
  `model` varchar(128) DEFAULT NULL,
  `price` int(10) UNSIGNED DEFAULT NULL
);
INSERT INTO `cars` (`brand`, `model`, `value`) VALUES
('Volvo', 'S40', 1),
('Porsche', 'Carrera', 4),
('Opel', 'Corsa', 3),
('Renault', 'Clio', 1),
('Renault', 'Megane', 3),
('Volvo', 'V40', 2);
INSERT INTO `used_cars` (`brand`, `model`, `price`) VALUES
('Volvo', 'S40', 2500),
('Porsche', 'Carrera', 7500);
SELECT (@row := @row + 1) AS score,
   qry.brand,
   qry.model,
   qry.used_present
FROM
  (SELECT cars.brand,
          cars.model,
          (used_cars.price IS NOT NULL) AS used_present
   FROM cars
   LEFT JOIN used_cars ON used_cars.model = cars.model
                          AND used_cars.brand = cars.brand
ORDER BY `price` LIMIT 18446744073709551615) AS qry
CROSS JOIN
  (SELECT @row:=0) AS r
ORDER BY qry.used_present DESC,
         qry.brand ASC


score
brand
model
used_present


1
Porsche
Carrera
1

2
Volvo
S40
1

3
Opel
Corsa
0

4
Renault
Clio
0

5
Renault
Megane
0

6
Volvo
V40
0

score	brand	model	used_present
1	Porsche	Carrera	1
2	Volvo	S40	1
3	Opel	Corsa	0
4	Renault	Clio	0
5	Renault	Megane	0
6	Volvo	V40	0