MIN() Over Multiple Columns

[winston1984]

I need to find out how to find the minimum value usig a query over three columns:

SterlingTradeGross
BlowOutNett
SterlingTradeQGross

These are the three columns, I need a query which calculates the minimum value from all of these, how do I do it?

 

[dalchri]

If you have MySQL version 4 or later you can do it as follows:

SELECT MIN(Minimum)
FROM (
   SELECT MIN(SterlingTradeGross) AS Minimum
   FROM MyTable
   UNION ALL
   SELECT MIN(BlowOutNett) AS Minimum
   FROM MyTable
   UNION ALL
   SELECT MIN(SterlingTradeQGross) AS Minimum
   FROM MyTable) AS Subquery

If you don't have version 4 or later, you would need to make a temporary table with the results of the subquery so you could requery it with the first query.

 

[rac2]

If your version supports the CASE expression you could calculate the minimum value in each row, then use the aggregate function to find the minimum over the rows. This would permit using the GROUP BY to obtain breakdowns as well.

SELECT MIN(
      CASE
        WHEN SterlingTradeGross < BlowOutNett
             AND SterlingTradeGross < SterlingTradeQGross
          THEN SterlingTradeGross
        WHEN BlowOutNett < SterlingTradeGross
             AND BlowOutNett < SterlingTradeQGross
          THEN BlowOutNett
        ELSE SterlingTradeQGross
      END
   ) AS LowBid
FROM MyTable

 

[r937]

sheesh, for once mysql has a decent function that should be (but isn't) standard sql and nobody seems to know about it

if you want the minimum value on each row,

select least(SterlingTradeGross
            ,BlowOutNett
            ,SterlingTradeQGross)
  from yourtable

if you want the smallest value in the entire table,

select min(
       least(SterlingTradeGross
            ,BlowOutNett
            ,SterlingTradeQGross)
           )
  from yourtable

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[winston1984]

Thats great, Least is what I was after. However, there's a BUT...

Some of the values in the BlowutNett field are 0.0 (float), I want to ignore these, any ideas?

 

[r937]

add a WHERE Clause

select least(columns)
where somecolumn > 0

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[winston1984]

Unfortunately that is not possible, as I am getting other products as well, here is my query. I need to filter to products > 0.0 in BlowOutNett without touching the WHERE clause:

SELECT LEAST(SterlingTradeGross , CASE WHEN BlowOutNett <= 0.0 THEN 9999999 ELSE BlowOutNett END, SterlingQTradeGross, newPrice ) AS TradePrice FROM specials AS s INNER JOIN Product AS p ON p.IDPartNumber = s.IDPartNumber, ProductInfo AS i, ProductInfo AS i2 WHERE p.IDPartNumber = i2.IDPartNumber AND i.GenericGroup = i2.GenericGroup AND i.IDPartNumber = '".$IDPartNumber."'"

But the CASE doesn't seem to work.

 

[r937]

your CASE looks okay, and CASER inside LEAST does work, i just tested it

what does "doesn't seem to work" mean?

no results? syntax error? crashes the server?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[winston1984]

it doesn't limit the results in any way, it still brings back the 0.0's.

 

[r937]

are you sure the 0.0s aren't coming from one of the other columns in the LEAST?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)