CODEWITHSUNDEEP
mysqlsql
Seevali.Weblook
Seevali.Weblook

Reputation: 23

MySQL: Get the MIN value of a table from all columns & rows

Refer this sqlfiddle.com/#!2/04c61/4 (a table with multiple columns (decimal)).

And I want to get the minimum value skipping 0 zero values from all cells in that table.

I tried this sql

SELECT
LEAST(
MIN(IF(sgl_ro=0,'NULL',sgl_ro))
,MIN(IF(sgl_bb=0,'NULL',sgl_bb))
,MIN(IF(sgl_hb=0,'NULL',sgl_hb))
,MIN(IF(sgl_fb=0,'NULL',sgl_fb))
) AS MinRate
FROM room_rates
WHERE hotel_id='1'
GROUP BY hotel_id;

But it gives me wrong value (I think because of multiple rows. It returns the correct value if one row).

Thanks.

Upvotes: 1

Views: 981

Answers (2)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

Here's one solution:

SELECT least(MIN(nullif(sgl_ro,0))
            ,MIN(nullif(sgl_bb,0))
            ,MIN(nullif(sgl_hb,0))
            ,MIN(nullif(sgl_fb,0)) ) as min_rate
FROM room_rates
WHERE hotel_id='1'
;

EDIT: Use NULL instead of 'NULL'

'NULL' is a string and MySQL have very weird ideas on how to cast between types:

select case when 0 = 'NULL' 
            then 'ohoy' 
            else 'sailor' 
       end 
from room_rates;

ohoy
ohoy
ohoy

I.e. you solution will work fine by removing the ' from NULL:

SELECT
LEAST(
MIN(IF(sgl_ro=0,NULL,sgl_ro))
,MIN(IF(sgl_bb=0,NULL,sgl_bb))
,MIN(IF(sgl_hb=0,NULL,sgl_hb))
,MIN(IF(sgl_fb=0,NULL,sgl_fb))
) AS MinRate
FROM room_rates
WHERE hotel_id='1'
;

MINRATE
9

Edit: Comparison between DBMS:

I tested the following scenario for all DBMS availible in sqlfiddle + DB2 10.5:

create table t(x int);
insert into t(x) values (1);
select case when 0 = 'NULL' 
        then 'ohoy' 
        else 'sailor' 
   end 
from t;

All mysql versions returned 'ohoy'

sql.js returned 'sailor'

all others (including DB2 10.5) considered the query to be illegal.

Edit: handle situation where all columns in a row (or all rows for a column) = 0

select min(least(coalesce(nullif(sgl_ro,0), 2147483647)
                ,coalesce(nullif(sgl_bb,0), 2147483647)
                ,coalesce(nullif(sgl_hb,0), 2147483647) 
                ,coalesce(nullif(sgl_fb,0), 2147483647) ) )  
FROM room_rates
WHERE hotel_id='1'
  AND coalesce(nullif(sgl_ro,0), nullif(sgl_bb,0)
              ,nullif(sgl_hb,0), nullif(sgl_fb,0)) IS NOT NULL;

Upvotes: 2

Rimas
Rimas

Reputation: 6024

Another solution step by step:

Get min value from one column:

SELECT MIN(sgl_ro) AS MinRate, hotel_id
   FROM room_rates
   WHERE sgl_ro != 0 AND hotel_id='1'
   GROUP BY hotel_id

Get min values from all columns and aggregate to one min value (final query):

SELECT MIN(MinRate) AS MinRate
FROM (
  SELECT MIN(sgl_ro) AS MinRate, hotel_id
     FROM room_rates
     WHERE sgl_ro != 0 AND hotel_id='1'
     GROUP BY hotel_id
  UNION 
  SELECT MIN(sgl_bb) AS MinRate, hotel_id
     FROM room_rates
     WHERE sgl_bb != 0 AND hotel_id='1'
     GROUP BY hotel_id
  UNION 
  SELECT MIN(sgl_hb) AS MinRate, hotel_id
     FROM room_rates
     WHERE sgl_hb != 0 AND hotel_id='1'
     GROUP BY hotel_id
  UNION 
  SELECT MIN(sgl_fb) AS MinRate, hotel_id
     FROM room_rates
     WHERE sgl_fb != 0 AND hotel_id='1'
     GROUP BY hotel_id
  ) res
GROUP BY hotel_id

Test it: http://sqlfiddle.com/#!2/3b156/1

Upvotes: 1

Related Questions