title: Exercise 24 - Find the model number of the product (PC, laptop, or printer) with the highest price. Result set: model
body: <p>I have this SQL statement and it returns the correct answer but it's too long as I can't figure out how to reuse the alias, so I had wrote the whole SELECT again:</p>
  
  <pre><code>select model from
  (
  select model, max(price) as maxt from
  (
  select model,price from pc where price in (select max(price) from pc)
  union
  select model,price from laptop where price in (select max(price) from laptop)
  union
  select model,price from printer where price in (select max(price) from printer)
  ) as ab
  group by model
  ) as ba
  where maxt in 
  
  (
  select max(maxt) from (
  
  select model, max(price) as maxt from
  (
  select model,price from pc where price in (select max(price) from pc)
  union
  select model,price from laptop where price in (select max(price) from laptop)
  union
  select model,price from printer where price in (select max(price) from printer)
  ) as aba
  group by model
  ) as bac )
  </code></pre>
  
  <p>The exercise is :
  Find the model number of the product (PC, laptop, or printer) with the highest price.
  Result set: model.</p>
  
  <p>Table Laptop</p>
  
  <pre><code>code    model   speed   ram hd      price       screen
  ------------------------------------------------------
  1       1298    350     32  4.0     700.0000    11
  2       1321    500     64  8.0     970.0000    12
  3       1750    750     128 12.0    1200.0000   14
  4       1298    600     64  10.0    1050.0000   15
  5       1752    750     128 10.0    1150.0000   14
  6       1298    450     64  10.0    950.0000    12
  </code></pre>
  
  <p>The PC table:</p>
  
  <pre><code>code     model  speed   ram hd  cd  price
  -------------------------------------------------------
  1   1232    500 64  5.0 12x 600.0000
  10  1260    500 32  10.0    12x 350.0000
  11  1233    900 128 40.0    40x 980.0000
  12  1233    800 128 20.0    50x 970.0000
  2   1121    750 128 14.0    40x 850.0000
  3   1233    500 64  5.0 12x 600.0000
  4   1121    600 128 14.0    40x 850.0000
  5   1121    600 128 8.0 40x 850.0000
  6   1233    750 128 20.0    50x 950.0000
  7   1232    500 32  10.0    12x 400.0000
  8   1232    450 64  8.0 24x 350.0000
  9   1232    450 32  10.0    24x 350.0000
  </code></pre>
  
  <p>Printer table:</p>
  
  <pre><code>code    model   color   type    price
  -----------------------------------------
  1   1276    n   Laser   400.0000
  2   1433    y   Jet 270.0000
  3   1434    y   Jet 290.0000
  4   1401    n   Matrix  150.0000
  5   1408    n   Matrix  270.0000
  6   1288    n   Laser   400.0000
  </code></pre>
  
  <p>Table Product:</p>
  
  <pre><code>maker   model   Type
  -----------------------
  A       1232    PC
  A       1233    PC
  A       1276    Printer
  A       1298    Laptop
  A       1401    Printer
  A       1408    Printer
  A       1752    Laptop
  B       1121    PC
  B       1750    Laptop
  C       1321    Laptop
  D       1288    Printer
  D       1433    Printer
  E       1260    PC
  E       1434    Printer
  E       2112    PC
  E       2113    PC
  </code></pre>