title: SQL query: Select max value of subselect
body: <p>I've got a sql statement which already works, but i think there must be a better solution than mine.</p>
  
  <p>I'm trying to <strong>get</strong> the <strong>articles</strong> with the <strong>highest price</strong> which have <strong>never been sold</strong>.</p>
  
  <p>With this select I'm getting all the Articles (number + price) that haven't been sold yet:</p>
  
  <pre><code>select anr, price
  from article a 
  where not exists(
      select 1 from OrderItems o
      where o.artnr = a.anr 
    )
  </code></pre>
  
  <p>The article number + price result looks like</p>
  
  <pre><code>| Anr | Price |
  | 1   | 300.0 |
  | 4   | 340.0 |
  | 5   | 340.0 |
  | 3   | 200.0 |
  </code></pre>
  
  <p>My temporary solution to get the articles with the highest price is:</p>
  
  <pre><code>select anr, price 
  from article 
  where anr in(
    select anr
    from article a 
    where not exists(
      select 1 from OrderItems o
      where o.artnr = a.anr 
    )
  )
  and price = (
    select max(price)
    from article a 
    where not exists(
      select 1 from OrderItems o
      where o.artnr = a.anr 
    )
  )
  </code></pre>
  
  <p>The correct solution is:</p>
  
  <pre><code>| Anr | Price |
  | 4   | 340.0 |
  | 5   | 340.0 |
  </code></pre>
  
  <p>Is there a way to avoid having the same subselect twice?</p>
  
  <p>For testing here is the shortened Create Table script with my insert values:</p>
  
  <pre><code>CREATE TABLE Article
  (
    Anr Int Primary Key,
    Price Numeric(9,2) Not Null
  );
  
  CREATE TABLE Orders
  (
    OrderNr Int Primary Key
  )
  
  CREATE TABLE OrderItems
  (
    OrderNr Int References Orders On Delete Cascade, 
    ItemNr  Int,
    Artnr   Int References Article Not Null,
    Amount  Int Not Null Check(Amount &gt;= 0),
    Primary Key(OrderNr, ItemNr)
  )
  
  -- articles without an order
  Insert into Article (Anr, Price) values(1,300.0);
  Insert into Article (Anr, Price) values(4,340.0);
  Insert into Article (Anr, Price) values(5,340.0);
  Insert into Article (Anr, Price) values(3,200.0);
  
  -- articles for order with orderNr '1'
  Insert into Article (Anr, Price) values(2,340.0);
  Insert into Article (Anr, Price) values(6,620.0);
  
  -- insert test order that contains the two articles
  Insert into Orders (OrderNr) values (1);
  Insert into OrderItems(OrderNr, ItemNr, Artnr, Amount) values(1,1,2,4);
  Insert into OrderItems(OrderNr, ItemNr, Artnr, Amount) values(1,2,6,2);
  </code></pre>
  
  <p>I also read the topic <a href="https://stackoverflow.com/questions/32673980/select-max-value-in-subquerry-sql">Select max value in subquery SQL</a>
  but I think in my case there must be a shorter way of doing the select.</p>