SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE wands (`id` int, `code` int, `coins_needed` int, `power` int) ; INSERT INTO wands (`id`, `code`, `coins_needed`, `power`) VALUES (1, 4, 3688, 8), (2, 3, 9364, 3), (3, 3, 7187, 10), (4, 3, 734, 8), (5, 1, 6020, 2), (6, 2, 6773, 7), (7, 3, 9873, 9), (8, 3, 7721, 7), (9, 1, 1647, 10), (10, 4, 504, 5), (11, 2, 7587, 5), (12, 5, 9897, 10), (13, 3, 4651, 8), (14, 2, 5408, 1), (15, 2, 6018, 7), (16, 4, 7710, 5), (17, 2, 8798, 7), (18, 2, 3312, 3), (19, 4, 7651, 6), (20, 5, 5689, 3) ; CREATE TABLE wands_property (`code` int, `age` int, `is_evil` int) ; INSERT INTO wands_property (`code`, `age`, `is_evil`) VALUES (1, 45, 0), (2, 40, 0), (3, 4, 1), (4, 20, 0), (5, 17, 0) ; (select id,coins_needed from wands) i join (SELECT wp.age,min(w.coins_needed) as coin,w.power FROM wands w JOIN wands_property wp USING (code) where wp.is_evil=0 GROUP BY 1,3 ORDER BY 3 desc, 1 desc) v on (i.coins_needed=v.coin)