title: Correlated Sub queries using MAX and GROUP BY
body: <p>I have a database with the tables: VEHICLES (a Blue Honda Civic costing $30k), BRAND (Honda) AND MODEL (Civic). </p>
  
  <p>I am trying to create a <strong>correlated sub query</strong> which will give me the VEH_IDs for the cars with the highest price from each BRAND. </p>
  
  <p>Simply, I want to know for all the brands which car costs the most. </p>
  
  <p>I've attached a picture with the SQL im trying but it's not working. Most likely because I have no idea what im doing. 
  <img src="https://i.stack.imgur.com/gUwOq.png" alt="enter image description here"></p>
  
  <pre><code>SELECT 
  BRAND.BRAND_ID, BRAND_NAME, VEHICLE.MODEL_ID, VEH_ID
  FROM BRAND, MODEL, VEHICLE
  WHERE VEH_PRICE = 
  ( 
  SELECT MAX(VEH_PRICE)
  FROM VEHICLE
  )
  GROUP BY BRAND.BRAND_ID, BRAND_NAME, VEHICLE.MODEL_ID, VEH_ID;
  </code></pre>