Finding the minimum of two columns from two different rows in a SQL table

Question

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.

I was able to find the difference but was not able to return the minimum value of two columns

I have the following table

 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

And I have the following SQL query and its results below

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<r2.i
AND r1.term!=r2.term

And Results are as follows fro the above query

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

But I am trying to include another 2 columns called BidVolume and OfferVolume and results should be something like following

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

What would be the best way to include them

You can get the lesser of two values with something like CASE WHEN m < n THEN m ELSE n END. — Joe Farrell
– Joe Farrell, Commented Jun 4, 2014 at 15:24

lc. · Accepted Answer · 2014-06-04 15:24:23Z

4

Aggregate functions, such as MIN are for aggregating across multiple rows, and will not do what you expect here (as you've already found out). Instead, you should use a case to choose which table and column to show from. Yes, I mean "table" here because your two joined rows are coming from separate logical tables, aliased t1 and t2.

For example, for BidVolume:

case when r1.BidVolume < r2.OfferVolume then r1.BidVolume else r2.OfferVolume end

answered Jun 4, 2014 at 15:24

lc.

117k21 gold badges163 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

DoIt Over a year ago

Please note that I am comparing two different columns in the table BidVolume and OfferVolume. Your answer compares same columns but different rows

Gordon Linoff Over a year ago

@Dev . . . This is comparing different columns in the same row.

lc. Over a year ago

Yes I saw that and fixed it with an edit. Sorry for the confusion

DoIt Over a year ago

@lc. your answer seems logical but it is not returning the correct results always. not sure why?

lc. Over a year ago

I'm not sure why either. I can't test it because your query refers to more columns than are in your example data, but I'm pretty sure this is what you are looking for.

|

spioter · Accepted Answer · 2014-06-04 16:47:07Z

0

Sorry -I misread the problem.

The real answer is that the first answer you received was close. The problem is its only comparing 2 of the 4 values.

In your SQL, you are always comparing 2 columns across 2 records - and you need the minimum of 4 different values, so in mysql, you would do this:

least( r1.bidVolume, r1.offerVolume, r2.bidVolume, r2.offerVolume) as Minimum_Volume

here's my original answer for posterity To get the aggregate minimum from two different columns, you need a) expression that obtains the minimum value from the two columns for a given row b) aggregate minimum of the above expression across all rows

For a), I will use mysql "IF" syntax, and you can adjust to the sql of your choice. The following query will select the minimum between the Bid and Offer volumes from the first table you posted on a per Market and per Grade basis:

select Market, Grade, min( if( BidVolume < OfferVolume, BidVolume, OfferVolume)) MinVol
from YourTable
group by Market, Grade
;

If you use the above as a template, you can adjust as needed for other business rules.

The results of the above would be:

Market  Grade       MinVol   
Heavy   ABC         1000
Sour    XYZ         1500

edited Jun 4, 2014 at 16:47

answered Jun 4, 2014 at 16:22

spioter

1,8701 gold badge13 silver badges22 bronze badges

2 Comments

DoIt Over a year ago

I am trying to find minimum of two different columns and two different rows.

spioter Over a year ago

that's what the sql i posted does, please see my edits

Collectives™ on Stack Overflow

Finding the minimum of two columns from two different rows in a SQL table

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related