SELECT
  ---- Dimension ----
  -- カタログ車両情報 --
  country_name, master_brand.name AS brand_name, -- from brand_master
  master_catalog.model, master_catalog.grade,    -- from catalog_master
  master_body.name AS body_name,                 -- from body_master
  series,                                        -- from catalog_master
  -- 販売車両情報 --
  id, color, "year", inspection, recycle, maintenance, warranty, warranty_distance, engine, -- from usedcar
  -- 販売店情報 --
  master_pref.name AS shop_pref_name, -- from pref_master
  shop_name, shop_lng, shop_lat,      -- from usedcar

  ---- Measure ----
  -- カタログ車両情報 --
  height, "length", width, person, master_catalog.price AS catalog_price,    -- from catalog_master
  -- 販売車両情報 --
  raw.price AS usedcar_price, odd_km, warranty_length_kikan, maintenance_fee -- from usedcar
FROM usedcar raw
JOIN
( SELECT code, name, country_name FROM brand_master ) master_brand
ON
raw.code = master_brand.code
JOIN
( SELECT code, name FROM body_master ) master_body
ON
raw.body_code = master_body.code
LEFT OUTER JOIN
(
  SELECT
    code, body_code, model, grade,
    MAX(series) AS series, MAX_BY(person,series) AS person, MAX_BY(price,series) AS price, MAX_BY(height,series) AS height, MAX_BY("length",series) AS "length",  MAX_BY(width,series) AS width
  FROM catalog_master
  GROUP BY code, body_code, model, grade
) master_catalog
ON
          raw.code = master_catalog.code
 AND raw.body_code = master_catalog.body_code
 AND     raw.model = master_catalog.model
 AND     raw.grade = master_catalog.grade
JOIN
( SELECT code, name, large_area_code FROM pref_master ) master_pref
ON
raw.shop_pref_code = master_pref.code
ORDER BY id
LIMIT 10