title: Finding the minimum of two columns from two different rows in a SQL table
body: <p>I am working on a SQL View which returns difference of two columns and minimum value of two columns that belongs to two different rows of single table.</p>
  
  <p>I was able to find the difference but was not able to return the minimum value of two columns</p>
  
  <p>I have the following table</p>
  
  <pre><code> id      Market  Grade       Term    BidVolume   Bid     Offer   OfferVolume
      1       Heavy   ABC         Jun14 1000     -19.5      -17         2500
      2       Heavy   ABC         Jul14 2000      -20      -17.5        1400 
      3       Sour    XYZ         Jun14 3000      -30       -17         2300  
      4       Sour    XYZ         Jul14 1500      -32       -27         2900
  </code></pre>
  
  <p>And I have the following SQL query and its results below</p>
  
  <pre><code>CREATE VIEW [dbo].[InferredBids] AS
  WITH numbered AS
    ( SELECT id, product, grade, term, bid, offer, termid, bidVolume, offerVolume,
             row_number() OVER (Partition BY Product, Grade ORDER BY termid) i
     FROM dbo.CanadianCrudes) --select * from numbered
  SELECT r1.id AS Id,
         r1.product + '/' + r1.grade AS Market,
         r1.term + '/' + r2.term AS Term,
         r1.Bid - r2.Offer [Bid], r1.Offer - r2.Bid [Offer]
  FROM numbered r1
  JOIN numbered r2 ON r1.product = r2.product
  AND r1.grade = r2.grade
  AND r1.termid+1=r2.termid
  AND r1.i&lt;r2.i
  AND r1.term!=r2.term
  </code></pre>
  
  <p>And Results are as follows fro the above query</p>
  
  <pre><code>Market     Term          Bid                   Offer
  Heavy/ABC  Jun14/Jul14   (-19.5-(-17.5))=-2    (-17-(-20))=3
  Sour/XYZ  Jun14/Jul14    (-30-(-27))=-3        (-17-(-32))=15
  </code></pre>
  
  <p>But I am trying to include another 2 columns called BidVolume and OfferVolume and results should be something like following</p>
  
  <pre><code>Market     Term          BidVolume                  Bid                   Offer        OfferVolume     
   Heavy/ABC  Jun14/Jul14  Min(1000,1400)=1000    (-19.5-(-17.5))=-2     (-17-(-20))=3  Min(2500,2000)=2000
   Sour/XYZ  Jun14/Jul14    Min(3000,2900)=2900   (-30-(-27))=-3        (-17-(-32))=15   Min(2300,1500)=1500
  </code></pre>
  
  <p>What would be the best way to include them </p>