clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335936 fiddles created (27471 in the last week).

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 );
 hidden batch(es)


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);
 hidden batch(es)


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
 hidden batch(es)